Fragmentation queries

 

1. Identify the LOBS were securefile is NO.

select owner,table_name,column_name,securefile from dba_lobs where owner not in ('SYS','SYSTEM','OUTLN','XDB','AUDSYS') and securefile='NO';

2. identify fragmentation on table level

select * from (select owner, table_name, round(((blocks*16/1024)),2) as TOTAL_SIZE_MB, round(((num_rows*avg_row_len/1024/1024)),2) as ACTUAL_SIZE_MB, round(((blocks*16/1024) - (num_rows*avg_row_len/1024/1024)),2) as FRAGMENTED_SPACE_MB,  round(((blocks*16/1024) - (num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2)*100 "PERCENTAGE" from all_tables where owner not in ('SYS','SYSTEM','OUTLN','XDB','AUDSYS','DBSNMP','APPQOSYS','CTXSYS','RDSADMIN','PERSTAT','OPS$ORACLE')) where FRAGMENTED_SPACE_MB > 10 order by FRAGMENTED_SPACE_MB desc;

3. check the total LOBS size

select b.owner,table_name,column_name,segment_name,a.bytes/1024/1024/1024 as Size_GB from dba_segments a join dba_lobs b using (segment_name) where b.owner not in ('SYS','SYSTEM','OUTLN','XDB','AUDSYS') and a.owner=b.owner order by Size_GB desc;

4. original LOBS size

select sum(dbms_lobs.getlength("<column_name>"))/1024/1024/1024 from <schema>.<table_name>;