Tag Archives: script for partition index

Rebuild the global or local partition index in Oracle

Rebuild the global or local index in Oracle

Local and global index present in partition table of the oracle. Index is created on basis of partition is local and index created on whole table is global index.
You cannot rebuild the local index which having partition as normal. If you tried to do you are getting the following error:

SQL> alter index sales_IDX rebuild;
alter index sales_IDX rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

Rebuild partition index by specifying partition name.

ALTER INDEX sales_IDX REBUILD PARTITION sales_Q4 TABLESPACE users;

Rebuild Global index as normal

ALTER INDEX sales_ids REBUILD;

Check the index is local or global:

select INDEX_NAME,TABLE_NAME,INDEX_type ,GLOBAL_STATS from dba_indexes where table_name = 'EMP';

Check the partition index

select index_name, partition_name,status from dba_ind_partitions;

Rebuild the partition local index with script

-- Offline rebuild
SET LINESIZE 5000;
SET PAGESIZE 5000;
SET LONG 50000;
SET TRIMSPOOL ON
SET WRAP ON
set termout off
set verify off
set longchunksize 200000
set feedback off
SET HEADING Off
set echo off
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from dba_IND_PARTITIONS where INDEX_OWNER='HR';

-- Online rebuild
SET LINESIZE 5000;
SET PAGESIZE 5000;
SET LONG 50000;
SET TRIMSPOOL ON
SET WRAP ON
set termout off
set verify off
set longchunksize 200000
set feedback off
SET HEADING Off
set echo off
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||' online ;' from dba_IND_PARTITIONS where INDEX_OWNER='HR';

Advertisements