set pagesize 1000 set linesize 300 col tablespace_name for a20 col owner for a10 col segment_cnt for 999999 col blocks for 99999999999 col kb for 999999 select sg.tablespace_name, sg.owner, sg.segment_type, count(segment_name) segment_cnt, sum(blocks) blocks, round(sum(bytes)/1024/1024) mb from dba_segments sg, dba_tablespaces ts where sg.tablespace_name = ts.tablespace_name group by sg.tablespace_name, sg.owner, sg.segment_type order by sg.tablespace_name, sg.owner, sg.segment_type;
実行してみるとこんな感じ。
SQL> r 1 select sg.tablespace_name, 2 sg.owner, 3 sg.segment_type, 4 count(segment_name) segment_cnt, 5 sum(blocks) blocks, 6 round(sum(bytes)/1024/1024) mb 7 from dba_segments sg, dba_tablespaces ts 8 where sg.tablespace_name = ts.tablespace_name 9 group by sg.tablespace_name, sg.owner, sg.segment_type 10* order by sg.tablespace_name, sg.owner, sg.segment_type Database Freespace Summary TABLESPACE_NAME OWNER SEGMENT_TYPE SEGMENT_CNT BLOCKS MB -------------------- ---------- ------------------ ----------- ------------ ---------- SYSAUX DBSNMP INDEX 8 64 1 SYSAUX DBSNMP TABLE 17 136 1 SYSAUX EXFSYS INDEX 36 288 2 SYSAUX EXFSYS LOBINDEX 2 16 0 SYSAUX EXFSYS LOBSEGMENT 2 16 0 SYSAUX EXFSYS TABLE 18 144 1 SYSAUX MDSYS INDEX 38 424 3 SYSAUX MDSYS LOBINDEX 16 128 1 SYSAUX MDSYS LOBSEGMENT 16 1280 10 SYSAUX MDSYS TABLE 32 976 8 SYSAUX ORDSYS INDEX 4 32 0 SYSAUX ORDSYS TABLE 4 32 0 SYSAUX SYS INDEX 214 16984 133 SYSAUX SYS INDEX PARTITION 200 3976 31 SYSAUX SYS LOB PARTITION 1 8 0 SYSAUX SYS LOBINDEX 35 280 2 SYSAUX SYS LOBSEGMENT 35 3840 30 SYSAUX SYS NESTED TABLE 1 8 0 SYSAUX SYS TABLE 177 11080 87 SYSAUX SYS TABLE PARTITION 189 6768 53 SYSAUX SYSMAN INDEX 398 3888 30 SYSAUX SYSMAN LOBINDEX 28 224 2 SYSAUX SYSMAN LOBSEGMENT 28 224 2 SYSAUX SYSMAN NESTED TABLE 2 16 0 SYSAUX SYSMAN TABLE 312 2928 23 SYSAUX SYSTEM INDEX 14 112 1 SYSAUX SYSTEM INDEX PARTITION 32 256 2 SYSAUX SYSTEM LOBINDEX 7 56 0 SYSAUX SYSTEM LOBSEGMENT 7 56 0 SYSAUX SYSTEM TABLE 22 176 1 SYSAUX SYSTEM TABLE PARTITION 27 216 2 ...
