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...!!!
###########################################
Comments