spool index_rebuild.sql
select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
from dba_indexes
where owner=upper('SAMPLE');
spool off
You actually make it seem really easy with your presentation but I to find this topic to be actually something that I think I might never understand. It kind of feels too complex and extremely wide for me. I am looking forward on your next post, I’ll attempt to get the hold of it!
select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’ from dba_indexes where owner=upper(‘OWNER’);
SELECT ‘ALTER INDEX ‘||INDEX_owner||’.’||index_name ||’ REBUILD PARTITION ‘||PARTITION_NAME||’ ;’ FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER=’OWNER’;
SELECT ‘ALTER INDEX ‘||INDEX_owner||’.’||index_name ||’ REBUILD SUBPARTITION online nologging; ‘||SUBPARTITION_NAME||’ ;’ FROM DBA_IND_SUBPARTITIONS WHERE INDEX_OWNER=’OWNER’;
Declare
Cursor REF_INDEXES IS
Select INDEX_NAME
From ALL_INDEXES
Where TABLE_OWNER = ‘SAMPLE’
Order By 1;
Begin
DBMS_OUTPUT.PUT_LINE(‘Reindex Tables:’);
For IDX In REF_INDEXES
Loop
Begin
EXECUTE IMMEDIATE ‘Alter Index ‘ || 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;
4 comments
What about partitioned indexes?
Btw, it’s faster to rebuild in parallel. And don’t forget to restore the value of degree after rebuild.
You actually make it seem really easy with your presentation but I to find this topic to be actually something that I think I might never understand. It kind of feels too complex and extremely wide for me. I am looking forward on your next post, I’ll attempt to get the hold of it!
select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’ from dba_indexes where owner=upper(‘OWNER’);
SELECT ‘ALTER INDEX ‘||INDEX_owner||’.’||index_name ||’ REBUILD PARTITION ‘||PARTITION_NAME||’ ;’ FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER=’OWNER’;
SELECT ‘ALTER INDEX ‘||INDEX_owner||’.’||index_name ||’ REBUILD SUBPARTITION online nologging; ‘||SUBPARTITION_NAME||’ ;’ FROM DBA_IND_SUBPARTITIONS WHERE INDEX_OWNER=’OWNER’;
I like this one better with a cursor
Set SERVEROUTPUT ON;
Declare
Cursor REF_INDEXES IS
Select INDEX_NAME
From ALL_INDEXES
Where TABLE_OWNER = ‘SAMPLE’
Order By 1;
Begin
DBMS_OUTPUT.PUT_LINE(‘Reindex Tables:’);
For IDX In REF_INDEXES
Loop
Begin
EXECUTE IMMEDIATE ‘Alter Index ‘ || 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;