LLI Wiki — My Digital Knowledge Base

Oracle: rebuild and list indexes

PL/SQL blocks to rebuild unusable indexes and queries to list indexes and their columns.

Queries and PL/SQL blocks to rebuild unusable indexes, rebuild all indexes of a schema, and list the indexes of a table or the columns of an index.

Rebuild the unusable indexes of the current schema

The following block rebuilds every index of the current schema whose status is UNUSABLE:

set serveroutput on;
begin
  for cur_r in (select index_name from user_indexes
                where status = 'UNUSABLE'
               )
  loop
    dbms_output.put_line('Rebuilding index ' || cur_r.index_name);
    execute immediate 'alter index ' || cur_r.index_name || ' rebuild';
  end loop;
end;
/

Example output:

Rebuilding index I2T
Rebuilding index I1T

Rebuild all indexes of an owner

This block rebuilds every index on tables owned by APP_OWNER. An error on one index is displayed and the processing continues:

Declare
Cursor REF_INDEXES IS
Select TABLE_OWNER,INDEX_NAME
From ALL_INDEXES
Where TABLE_OWNER = 'APP_OWNER'
Order By 1;

Begin
DBMS_OUTPUT.PUT_LINE('Reindex Tables:');
For IDX In REF_INDEXES
Loop
Begin
DBMS_OUTPUT.PUT_LINE('Alter Index '|| IDX.TABLE_OWNER || '.' || IDX.INDEX_NAME || ' Rebuild');
EXECUTE IMMEDIATE 'Alter Index '|| IDX.TABLE_OWNER || '.' || IDX.INDEX_NAME || ' Rebuild';
EXCEPTION
When OTHERS Then
DBMS_OUTPUT.PUT_LINE('** Exception Reindexing Table ' || IDX.index_name || '; Error:' || SQLERRM);
End;
End Loop;
COMMIT;
End;

List the indexes of a table

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS
from ALL_INDEXES
where TABLE_NAME = 'APP_TABLE';

List the columns of an index

column index_name format a10;
column table_name format a10;
column column_name format a10;
column column_position format 99999;

SELECT
    index_name, table_name, column_name, column_position
FROM DBA_IND_COLUMNS
WHERE
    INDEX_OWNER = 'APP_OWNER'
    AND TABLE_NAME = 'APP_TABLE'
ORDER BY
    table_name,
    index_name,
    column_position;

Notes

Mastodon LLIMastodon SocialFosstodon