Rebuild All Indexes on a Schema in Oracle

spool index_rebuild.sql
select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
from dba_indexes
where owner=upper('SAMPLE');
spool off

 

Category: DatabaseSQLUncategorized

Tags:

4 comments

  1. What about partitioned indexes?
    Btw, it’s faster to rebuild in parallel. And don’t forget to restore the value of degree after rebuild.

  2. 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!

  3. 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’;

  4. 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;

Leave a Reply

Article by: Shadab Mohammad