Indexes Rebuild in 10g/11g/12C

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;'||chr(10)||
from dba_indexes where owner=upper('hr');

---- user_indexes
----  dba_indexes
----  all_indexes
---- set autotrace on explain ( Execution plan )

'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.