Andrew Fraser DBA

Space usage lob sub partitions

Querying partition space usage is a little awkward with lobs and sub partitions, but can be done with sql like this:

WITH base AS (
   SELECT lp.table_owner
        , lp.table_name
        , lp.partition_name
        , SUM ( s.bytes ) / 1024 / 1024 AS lob_mb
        , COUNT(*) AS num_subpartitions
     FROM dba_lob_partitions lp
     JOIN dba_lob_subpartitions lsp ON lsp.table_owner = lp.table_owner AND lsp.table_name = lp.table_name AND lsp.lob_partition_name = lp.lob_partition_name
     JOIN dba_segments s ON s.owner = lsp.table_owner AND s.segment_name = lsp.lob_name AND s.partition_name = lsp.lob_subpartition_name
    GROUP BY lp.table_owner , lp.table_name , lp.partition_name
)
SELECT tp.table_owner
     , tp.table_name
     , tp.partition_name
     , tp.high_value
     , base.num_subpartitions
     , tp.num_rows
     , tp.blocks * 8 / 1024 AS table_mb
     , base.lob_mb
     , tp.last_analyzed
  FROM dba_tab_partitions tp
  JOIN base ON base.table_owner = tp.table_owner AND base.table_name = tp.table_name AND base.partition_name = tp.partition_name
 WHERE tp.table_owner = 'MYSCHEMA'
 ORDER BY tp.table_owner , tp.table_name , tp.partition_name DESC
;