index

SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

  -- User-configurable variables

  v_schema VARCHAR2(30) := 'YOUR_SCHEMA_NAME'; -- Replace with your schema name

  v_batch_size NUMBER := 1000; -- Batch size for progress reporting

  

  -- Variables for logging and timing

  v_start_time TIMESTAMP;

  v_index_count NUMBER := 0;

  v_processed_count NUMBER := 0;

  v_error_count NUMBER := 0;

  v_sql VARCHAR2(4000);

  

  -- Function to get index columns as a comma-separated string

  FUNCTION get_index_columns(p_index_name VARCHAR2) RETURN CLOB IS

    v_columns CLOB;

  BEGIN

    SELECT /*+ PARALLEL(8) */ LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_position)

    INTO v_columns

    FROM all_ind_columns

    WHERE index_owner = v_schema

      AND index_name = p_index_name;

    RETURN NVL(v_columns, 'N/A');

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      RETURN 'N/A';

    WHEN OTHERS THEN

      RETURN 'Error retrieving columns: ' || SQLERRM;

  END;

  

BEGIN

  v_start_time := SYSTIMESTAMP;

  DBMS_OUTPUT.PUT_LINE('Starting index retrieval for schema ' || v_schema || ' at ' || TO_CHAR(v_start_time, 'YYYY-MM-DD HH24:MI:SS'));

  

  -- Count total indexes for progress tracking

  BEGIN

    SELECT /*+ PARALLEL(16) */ COUNT(*)

    INTO v_index_count

    FROM all_indexes

    WHERE owner = v_schema;

    DBMS_OUTPUT.PUT_LINE('Total indexes to process: ' || v_index_count);

  EXCEPTION

    WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('Error counting indexes: ' || SQLERRM);

      v_index_count := 0;

      -- Continue to avoid script termination

  END;

  

  -- Process indexes

  FOR idx IN (

    SELECT /*+ PARALLEL(16) */ 

           index_name, index_type, table_name, uniqueness,

           CASE WHEN partitioned = 'YES' THEN 'PARTITIONED' ELSE 'NON-PARTITIONED' END AS partition_status,

           status

    FROM all_indexes

    WHERE owner = v_schema

    ORDER BY index_name

  ) LOOP

    BEGIN

      -- Increment processed count

      v_processed_count := v_processed_count + 1;

      

      -- Get index columns

      v_sql := get_index_columns(idx.index_name);

      

      -- Output index details

      DBMS_OUTPUT.PUT_LINE(

        'Index: ' || idx.index_name || 

        ' | Type: ' || idx.index_type || 

        ' | Table: ' || idx.table_name || 

        ' | Columns: ' || v_sql || 

        ' | Unique: ' || idx.uniqueness || 

        ' | Partition: ' || idx.partition_status || 

        ' | Status: ' || idx.status

      );

      

      -- Output progress every v_batch_size indexes

      IF MOD(v_processed_count, v_batch_size) = 0 THEN

        DBMS_OUTPUT.PUT_LINE('Processed ' || v_processed_count || ' indexes, ' || (v_index_count - v_processed_count) || ' remaining');

      END IF;

      

    EXCEPTION

      WHEN OTHERS THEN

        v_error_count := v_error_count + 1;

        DBMS_OUTPUT.PUT_LINE(

          'Error processing index ' || idx.index_name || 

          ' | Type: ' || NVL(idx.index_type, 'N/A') || 

          ' | Table: ' || NVL(idx.table_name, 'N/A') || 

          ' | Error: ' || SQLERRM

        );

        CONTINUE;

    END;

  END LOOP;

  

  -- Summary output

  DBMS_OUTPUT.PUT_LINE('Index retrieval completed in ' || 

    ROUND(EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start_time)), 2) || ' seconds');

  DBMS_OUTPUT.PUT_LINE('Total indexes processed: ' || v_processed_count);

  DBMS_OUTPUT.PUT_LINE('Errors encountered: ' || v_error_count);

  

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Fatal error in script: ' || SQLERRM);

    RAISE;

END;

/