Friday, November 8, 2019

How to list subpartitions and their LOB segment sizes




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