Rebuild All Indexes for specific User/Schema

Here, I will generate script to rebuild all index for a specific Schema. First of all, let’s all Index for a specific Owner.


SQL> select index_name from dba_indexes where owner=upper('hr');

INDEX_NAME
------------------------------
LOC_CITY_IX
LOC_ID_PK
REG_ID_PK
COUNTRY_C_ID_PK
LOC_STATE_PROVINCE_IX
LOC_COUNTRY_IX
DEPT_ID_PK
DEPT_LOCATION_IX
JOB_ID_PK
EMP_EMAIL_UK
EMP_EMP_ID_PK
EMP_DEPARTMENT_IX
EMP_JOB_IX
EMP_MANAGER_IX
EMP_NAME_IX
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX

19 rows selected.


Use this following script to generate Index Rebuild script for your Schema.


SQL> select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
  2  from dba_indexes
  3  where owner=upper('hr');

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINENOLOGGING;'
---------------------------------------------------------------------------------------------------
alter index HR.LOC_CITY_IX rebuild online nologging;
alter index HR.LOC_ID_PK rebuild online nologging;
alter index HR.REG_ID_PK rebuild online nologging;
alter index HR.COUNTRY_C_ID_PK rebuild online nologging;
alter index HR.LOC_STATE_PROVINCE_IX rebuild online nologging;
alter index HR.LOC_COUNTRY_IX rebuild online nologging;
alter index HR.DEPT_ID_PK rebuild online nologging;
alter index HR.DEPT_LOCATION_IX rebuild online nologging;
alter index HR.JOB_ID_PK rebuild online nologging;
alter index HR.EMP_EMAIL_UK rebuild online nologging;
alter index HR.EMP_EMP_ID_PK rebuild online nologging;
alter index HR.EMP_DEPARTMENT_IX rebuild online nologging;
alter index HR.EMP_JOB_IX rebuild online nologging;
alter index HR.EMP_MANAGER_IX rebuild online nologging;
alter index HR.EMP_NAME_IX rebuild online nologging;
alter index HR.JHIST_EMP_ID_ST_DATE_PK rebuild online nologging;
alter index HR.JHIST_JOB_IX rebuild online nologging;
alter index HR.JHIST_EMPLOYEE_IX rebuild online nologging;
alter index HR.JHIST_DEPARTMENT_IX rebuild online nologging;

19 rows selected.


Finally, execute above output to rebuild your all Indexes.


SQL> alter index HR.LOC_CITY_IX rebuild online nologging;

Index altered.

SQL> alter index HR.LOC_ID_PK rebuild online nologging;

Index altered.

SQL> alter index HR.REG_ID_PK rebuild online nologging;

Index altered.


Thanks
A Rahim Khan

Advertisements
    • Ruban
    • November 25th, 2013

    Can i do it in a procedure?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: