Script to Analyze Index Fragmentation and Generate Maintenance Scripts

advertisements

_____________________________________________________________________________________________________________________

This script analyzes the fragmentation of indexes with clustering factors greater than 1000 and generates maintenance scripts based on the fragmentation level. It uses a PL/SQL block to iterate over the indexes, and for each index, it determines whether to rebuild it (if clustering factor > 2000) or reorganize it (if clustering factor <= 2000). The generated scripts are displayed using the DBMS_OUTPUT.PUT_LINE function.

advertisements

 

-- Generate index rebuild scripts for heavily fragmented indexes
SET SERVEROUTPUT ON

DECLARE
  v_index_name    VARCHAR2(100);
  v_table_name    VARCHAR2(100);
BEGIN
  FOR index_stats IN (
    SELECT index_name, table_name
    FROM all_indexes
    WHERE table_owner = 'YOUR_SCHEMA_NAME'
      AND clustering_factor > 1000
  ) LOOP
    v_index_name := index_stats.index_name;
    v_table_name := index_stats.table_name;

    DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || v_index_name || ' REBUILD TABLESPACE NEW_TABLESPACE;');
  END LOOP;
END;
/

_____________________________________________________________________________________________________________________

Website Stats