Advanced Scripts for Indexes - Part 5 Posted by MOHAMMED ABDUL HAMEED on 3:37 PM

Rebuild Index Syntax

Syntax: alter index index_name rebuild;

SQL> alter index emp_ename rebuild;

Index altered.

Rebuild Index of multiple users :

Syntax : Select  'alter index ' || OWNER ||'.'||INDEX_NAME || ' rebuild;' from all_indexes Where Owner = 'USER NAME'; 

SQL> Select  'alter index ' || OWNER ||'.'||INDEX_NAME || ' rebuild;' from all_indexes Where Owner = 'SCOTT'; 

alter index SCOTT.EMP_ENAME rebuild;
alter index SCOTT.DEPT_UNIQUE_INDEX rebuild;

Collect Statistics on an Index :

Syntax : ALTER INDEX index_name REBUILD COMPUTE STATISTICS;

SQL> alter index emp_ename REBUILD COMPUTE STATISTICS; 

Index altered.

Move Table and Index to Other Table Space

Syntax: 
ALTER TABLE schema.table_name MOVE TABLESPACE TABLESPACE_NAME;

SQL Code:
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || OWNER || ’ts’ ||';'
from ALL_TABLES
WHERE OWNER IN ('USER NAME', 'USER NAME', 'USER NAME');

Syntax: 
ALTER INDEX schema.index_name REBUILD TABLESPACE TABLESPACE_NAME;

SQL Code
select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || 'idx’;'
from ALL_INDEXES
WHERE OWNER IN ('USER NAME', 'USER NAME', 'USER NAME');

How to identify the index unusable

SQL Code:
select index_name, status, owner
from all_indexes
where STATUS = 'UNUSABLE';

How to rebuild index unusable

SQL Code:
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes

where STATUS = 'UNUSABLE';

Check Index Creation or Index Rebuild Date in Oracle

SQL> select table_name,index_name,last_analyzed from dba_indexes where table_name='xxxxxxx';

Note : Last_Analyzed column date changes when the index is rebuild.

Find index status of a Particular Table.

SQL> SELECT OWNER,TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='xxxxx';

How to find the index status of Particular Schema.

SQL> SELECT OWNER,TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE OWNER='xxxxxx';

Check particular index size

SQL> select segment_name,sum(bytes)/1024/1024 as "SIZE in MB" from user_segments where segment_name='XXXXX' group by segment_name;

Check all the indexes of a particular table.

SQL> SELECT DISTINCT TABLE_NAME,INDEX_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='xxxxxxx';

Find Global & Local Indexes in Oracle Partitions


SQL> select table_name,index_name,locality from user_part_indexes where table_name='XXXXX';