Použil jsem scripty os Spyrose.
SQL> SELECT tablespace_name, owner, segment_type "Object Type", 2 COUNT(owner) "Number of Objects", 3 ROUND(SUM(bytes) / 1024 / 1024, 2) "Total Size in MB" 4 FROM sys.dba_segments 5 WHERE tablespace_name IN ('SYSTEM') 6 GROUP BY tablespace_name, owner, segment_type 7 ORDER BY tablespace_name, owner, segment_type; TABLESPACE_NAME OWNER Object Type ------------------------------ ------------------------------ ------------------ Number of Objects Total Size in MB ----------------- ---------------- SYSTEM OUTLN INDEX 4 .25 SYSTEM OUTLN LOBINDEX 1 .06 SYSTEM OUTLN LOBSEGMENT 1 .06 TABLESPACE_NAME OWNER Object Type ------------------------------ ------------------------------ ------------------ Number of Objects Total Size in MB ----------------- ---------------- SYSTEM OUTLN TABLE 3 .19 SYSTEM SYS CLUSTER 9 40.25 SYSTEM SYS INDEX 640 8317.94 TABLESPACE_NAME OWNER Object Type ------------------------------ ------------------------------ ------------------ Number of Objects Total Size in MB ----------------- ---------------- SYSTEM SYS LOBINDEX 117 7.31 SYSTEM SYS LOBSEGMENT 117 18.06 SYSTEM SYS NESTED TABLE 16 1 TABLESPACE_NAME OWNER Object Type ------------------------------ ------------------------------ ------------------ Number of Objects Total Size in MB ----------------- ---------------- SYSTEM SYS ROLLBACK 1 .38 SYSTEM SYS TABLE 514 950.06 SYSTEM SYSTEM INDEX 138 8.63 TABLESPACE_NAME OWNER Object Type ------------------------------ ------------------------------ ------------------ Number of Objects Total Size in MB ----------------- ---------------- SYSTEM SYSTEM LOBINDEX 14 .88 SYSTEM SYSTEM LOBSEGMENT 14 .88 SYSTEM SYSTEM TABLE 88 5.5 15 rows selected. SQL> SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name 2 FROM DBA_SEGMENTS 3 WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION', 4 'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY', 'LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION') 5 AND TABLESPACE_NAME LIKE 'SYSTEM' 6 --AND SEGMENT_NAME LIKE 'P2010201%' 7 --AND partition_name LIKE 'P20100201%' 8 AND segment_type = 'INDEX' 9 AND OWNER = 'SYS' 10 AND ROUND(bytes/(1024*1024),2) > 100 11 ORDER BY bytes DESC; OWNER ------------------------------ SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE PARTITION_NAME SIZE_MB ------------------ ------------------------------ ---------- TABLESPACE_NAME ------------------------------ SYS IDX_THPG1_ARCH INDEX 2816 SYSTEM OWNER ------------------------------ SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE PARTITION_NAME SIZE_MB ------------------ ------------------------------ ---------- TABLESPACE_NAME ------------------------------ SYS IDX_THPG3_ARCH INDEX 2240 SYSTEM OWNER ------------------------------ SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE PARTITION_NAME SIZE_MB ------------------ ------------------------------ ---------- TABLESPACE_NAME ------------------------------ SYS IDX_THPG2_ARCH INDEX 1426 SYSTEM OWNER ------------------------------ SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE PARTITION_NAME SIZE_MB ------------------ ------------------------------ ---------- TABLESPACE_NAME ------------------------------ SYS IDX_THPG4_ARCH INDEX 1008 SYSTEM OWNER ------------------------------ SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE PARTITION_NAME SIZE_MB ------------------ ------------------------------ ---------- TABLESPACE_NAME ------------------------------ SYS NEW_INDEX_ARCH INDEX 486 SYSTEM OWNER ------------------------------ SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE PARTITION_NAME SIZE_MB ------------------ ------------------------------ ---------- TABLESPACE_NAME ------------------------------ SYS IDX_TH1_ARCH INDEX 184 SYSTEM 6 rows selected. SQL>