Thursday, June 17, 2021

How to list partitions and their LOB segment sizes

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