Script to Generate Index Rebuild Scripts for Heavily Fragmented Indexes

advertisements

_____________________________________________________________________________________________________________________

This script generates index rebuild scripts for heavily fragmented indexes with clustering factors greater than 1000. It uses a PL/SQL block to iterate over the indexes and generates an ALTER INDEX ... REBUILD statement for each index. The generated scripts are displayed using the DBMS_OUTPUT.PUT_LINE function. These advanced scripts provide additional functionality, such as generating maintenance scripts based on index fragmentation levels or specifically targeting heavily fragmented indexes for rebuild. Customize the schema name, tablespace, and any other parameters in the scripts to align with your environment and requirements.

advertisements

 

Note: Please exercise caution when executing index maintenance scripts, as they can have a significant impact on database performance and require appropriate privileges. It is recommended to thoroughly test and validate the scripts in a non-production environment before applying them to production systems.

-- 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