How to identify top fragmented tables in Oracle Database

If You are looking for fragmented tables in your database. The below procedure will help you identify the top 10 fragmented tables. Deploy the below script and get fragmented tables in a few seconds.

Deploy this table/procedure if you are looking for top fragmented tables in any Oracle Database.

###################################

Step1:

CREATE TABLE fragmented_tables_log (

log_date DATE,

log_output CLOB

);

Step2:

DECLARE

v_schema VARCHAR2(30);

v_segment_name VARCHAR2(100);

v_fragment_ratio NUMBER;

v_rank NUMBER := 1;

v_log_output CLOB := '';

BEGIN

v_log_output := 'Top 10 Fragmented Tables:' || chr(10);

FOR t IN (SELECT owner, table_name

FROM dba_tables

WHERE owner NOT IN ('ORDDATA', 'MDSYS','SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN','GSMADMIN_INTERNAL','WMSYS','CTXSYS','DVSYS','XDB')

ORDER BY blocks DESC)

LOOP

v_schema := t.owner;

v_segment_name := v_schema || '.' || t.table_name;

BEGIN

SELECT ratio_to_report(blocks) OVER () INTO v_fragment_ratio

FROM dba_segments

WHERE owner = v_schema

AND segment_name = t.table_name

AND segment_type = 'TABLE'; -- Add this line to filter by table segments only

IF v_fragment_ratio > 0.5 THEN

v_log_output := v_log_output || v_rank || '. ' || v_segment_name || ': ' || v_fragment_ratio || chr(10);

v_rank := v_rank + 1;

END IF;

EXIT WHEN v_rank > 10;

EXCEPTION

WHEN NO_DATA_FOUND THEN

-- do nothing

NULL;

END;

END LOOP;

INSERT INTO fragmented_tables_log (log_date, log_output)

VALUES (SYSDATE, v_log_output);

COMMIT;

END;

/

Step3:

select * from fragmented_tables_log;

Done...!!!

###########################################

#database #fragmentation #tables #data #oracle #oracledatabase #oracledbaonlinetraining