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
APP_OWNERandAPP_TABLEare generic names to replace with the real owner and table.- The first block only handles indexes of the connected schema (
user_indexes). The second block and the listing queries useALL_INDEXESandDBA_IND_COLUMNSand require the corresponding privileges.