The query below will list the lob segment names, the lob subpartition names, the table subpartition names and sizing information for any subpartitioned tables using LOB columns:
SELECT ts.table_owner "table 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 subpart name", lsp.column_name "lob name", lsp.compression "lob compression", lsp.subpartition_name "table subpartition name", ts.num_rows "num rows", lsp.tablespace_name "tablespace", ROUND(sum(s.bytes)/1024/1024/1024) "size GB" FROM dba_segments s JOIN dba_lob_subpartitions lsp ON (s.partition_name = lsp.lob_subpartition_name) JOIN DBA_TAB_SUBPARTITIONS ts ON (TS.SUBPARTITION_NAME = lsp.SUBPARTITION_NAME) WHERE lsp.table_name='DOCUMENTS' AND ts.table_name='DOCUMENTS' -- 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 lsp.subpartition_name like 'SYS_SUBP186786%' AND s.segment_name IN ( SELECT lpt.lob_name FROM dba_lob_partitions lpt WHERE lpt.table_name IN ( 'DOCUMENTS' ) ) GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lsp.COMPRESSION,lsp.subpartition_name, lsp.column_name,ts.num_rows,lsp.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 lsp.subpartition_name DESC;
Example output:
TABLE_NAME | segment name | segment type | lob type | lob subpart name | lob compression | table subpartition name | num rows | tablespace | size GB |
---|---|---|---|---|---|---|---|---|---|
MYTABLE | MYTABLE_LOB7 | LOB SUBPARTITION | SECUREFILE | KLM_DOCS_2018_01 | MEDIUM | KLM_SP_2018_01 | 164497 | DOCS2018 | 235 |
MYTABLE | MYTABLE_LOB7 | LOB SUBPARTITION | SECUREFILE | VISA_DOCS_2018_01 | MEDIUM | VISA_SP_2018_01 | 72405 | DOCS2018 | 76 |
MYTABLE | MYTABLE_LOB7 | LOB SUBPARTITION | SECUREFILE | KLM_DOCS_2017_10 | MEDIUM | KLM_SP_2017_10 | 16256 | DOCS2017 | 19 |
MYTABLE | MYTABLE_SMALL_LOB7 | LOB SUBPARTITION | SECUREFILE | KLM_DOCS_SMALL_2018_01 | MEDIUM | KLM_SP_2018_01 | 164497 | DOCS2018 | 18 |
MYTABLE | MYTABLE_SMALL_LOB7 | LOB SUBPARTITION | SECUREFILE | VISA_DOCS_SMALL_2018_01 | MEDIUM | VISA_SP_2018_01 | 72405 | DOCS2018 | 8 |
No comments:
Post a Comment