--1.CHECK
select a.* from DBA_PART_INDEXES a, DBA_TAB_PARTITIONS b where a.owner=B.TABLE_OWNER and a.table_name=B.TABLE_NAME and a.owner not like 'SYS%' and b.partition_name like '%20131231';
-- Script rebuild index partititon unusable
select 'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace INDX nologging parallel 8 online;' from dba_ind_partitions where status='UNUSABLE';
-- Script rebuild các partition index
--+ Bước 1: Rebuild
select 'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace INDX' ||substr(partition_name,5,4)||' nologging parallel 8 online;' from dba_ind_partitions where
index_owner='APP_OWNER'
and index_name in (
'index_name_1',
'index_name_2'
)
order by partition_name desc
;
--+ Bước 2: Nologging noparallel
alter index app_owner.index_name_1 noparallel nologging;
alter index app_owner.index_name_2 noparallel nologging;
--2. CREATE
CREATE INDEX user1.IDX_SUB_ID ON user1.table1
(SUB_ID)
NO LOGGING
LOCAL PARALLEL 4 ONLINE;
Alter index user1.IDX_SUB_ID noparallel;
--3. ADD PARTITON
--Khi thêm partition cho bảng thì index trong bảng đó sẽ tự động có partition, nhưng cần rebuild lại index
-- .REBUILD partition ---
ALTER INDEX idx_sales REBUILD PARTITION DATA20140401 TABLESPACE INDX2014;
-- chuyen sang tablspace khac đồng thời rebuild
alter index ITEM_IDX rebuild partition TEST_Q1_JAN tablespace PART1 online parallel 10 nologging;
-- Rebuild lại partition DATA201107 của index
alter index user1.WS_LOG_I1 rebuild partition DATA201107 online parallel 64 nologging;
select 'alter index ' || owner ||'.' || segment_name ||' rebuild partition ' || partition_name || ' tablespace INDX' || substr(partition_name,5,4) || ' nologging parallel 4 online;'
from dba_segments where segment_type like 'INDEX%' and owner='user1' and segment_name not like '%$%' and(tablespace_name like 'DATA2007%' or tablespace_name like 'DATA2008')
order by owner, segment_name, partition_name;
