Month: February 2022

I created the below SQL to identify the largest LOB objects and associate them back to the respective table and column:

set pages 99
set lines 200
col owner           format a12
col table_name      format a25
col column_name     format a25
col segment_name    format a30
col partition_name  format a15
SELECT      tc.owner,
            tc.table_name,
            tc.column_name,
            lb.segment_name,
            sg.partition_name,
            Round(( sg.bytes / (1024*1024*1024)),2) AS size_gb
FROM        dba_tab_cols tc
inner join  dba_lobs lb
        ON  lb.owner        =  tc.owner
       AND  lb.table_name   =  tc.table_name
       AND  lb.column_name  =  tc.column_name
inner join  dba_segments sg
        ON  sg.segment_name =  lb.segment_name
WHERE       tc.owner        = 'schema owner here'
  AND       tc.data_type LIKE '%LOB%'
  and       Round(( sg.bytes / (1024*1024*1024)),2) > 50
ORDER  BY 
            size_gb DESC,
            tc.owner,
            tc.table_name,
            tc.column_name,
            lb.segment_name
;