SELECT ts.table_owner "owner", ts.table_name "table name", s.SEGMENT_NAME "segment name", s.SEGMENT_TYPE "segment type", s.SEGMENT_SUBTYPE "lob type", s.PARTITION_NAME "lob part name", lp.column_name "lob name", lp.compression "lob compression", lp.partition_name "table part name", ts.num_rows "num rows", lp.tablespace_name "tablespace", ROUND(sum(s.bytes)/1024/1024/1024) "size GB" FROM dba_segments s JOIN dba_lob_partitions lp ON (s.partition_name = lp.lob_partition_name) JOIN DBA_TAB_PARTITIONS ts ON (TS.PARTITION_NAME = lp.PARTITION_NAME) WHERE lp.table_name='MYTABLE' AND ts.table_name='MYTABLE' -- To limit the output to a specific tablespace, uncomment line below AND s.tablespace_name='DATA1' -- To limit output to specific table subpartitions only, uncomment the following row --AND lp.subpartition_name like 'SYS_SUBP186786%' AND s.segment_name IN ( SELECT lpt.lob_name FROM dba_lob_partitions lpt WHERE lpt.table_name IN ( 'MYTABLE' ) ) GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lp.COMPRESSION,/*lp.subpartition_name*/lp.partition_name, lp.column_name,ts.num_rows,lp.tablespace_name /* To limit output to lob subpartitions with a specific size, uncomment the restriction above and change the operator to suit your needs ( <, >, =) or use BETWEEN x AND y */ -- HAVING ROUND(sum(s.bytes)/1024/1024/1024) between 1 and 10 ORDER BY 12 DESC;Output:
owner | table name | segment name | segment type | lob type | lob part name | lob name | lob compression | table part name | num rows | tablespace | size GB |
---|---|---|---|---|---|---|---|---|---|---|---|
JIM | MYTABLE | SYS_LOB0000079505C00019$$ | LOB PARTITION | SECUREFILE | SYS_LOB_P3273 | MYLOB1 | NO | SYS_P3270 | 864051 |
DATA1 | 20 |
JIM | MYTABLE | SYS_LOB0000079505C00019$$ | LOB PARTITION | SECUREFILE | SYS_LOB_P4684 | MYLOB1 | NO | SYS_P4681 | 593520 |
DATA1 | 17 |
JIM | MYTABLE | SYS_LOB0000079505C00019$$ | LOB PARTITION | SECUREFILE | SYS_LOB_P5692 | MYLOB1 | NO | SYS_P5689 | 2021989 |
DATA1 | 16 |
For queries against a subpartitioned table, see this post.
No comments:
Post a Comment