table_extents.sql

oracle dba 常用的管理脚本, 覆盖日常的系统管理.

--* File Name    : Table_Extents.sql
--* Author       : DR Timothy S Hall
--* Description  : Displays a list of tables having more than 1 extent.
--* Requirements : Access to the DBA views.
--* Call Syntax  : @Table_Extents (schema-name)
--* Last Modified: 15/07/2000
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT t.table_name,
       Count(e.segment_name) extents,
       t.max_extents,
       t.num_rows "ROWS",
       Trunc(t.initial_extent/1024) "INITIAL K",
       Trunc(t.next_extent/1024) "NEXT K"
FROM   all_tables t,
       dba_extents e
WHERE  e.segment_name = t.table_name
AND    e.owner        = t.owner
AND    t.owner        = Upper('&&1')
GROUP BY t.table_name,
         t.max_extents,
         t.num_rows,
         t.initial_extent,
         t.next_extent
HAVING   Count(e.segment_name) > 1
ORDER BY Count(e.segment_name) DESC;

SET PAGESIZE 18
SET VERIFY ON