Avoiding Oracle Global Index Recovery When Partition Moves

Question

Periodically, I need to separate a partition from one table and connect to another table in another table space.

Pseudo Stream:

• Loop each table and expired section:

  1.1 Create a new target partition in the target table

  1.2 Moving a partition to a target tablespace

  1.3 Exchange Exchange Source Partition to Temporary Table

  1.4 Exchange temp table with target partition

  1.5 Divide the source section

• Rebuild global indexes in the source table

• Rebuild global indexes on target table

The problem is that the recovery index makes the index temporarily unavailable, which is unacceptable to support a real-time application in my case.

- , , , - . , Partition Drop Partition Truncate. UNUSABLE Move Move Partition Exchange. Partition Drop .

, , . ?

:

• UPDATE GLOBAL INDEXES Exchange, , .
• , . , ( ).

Answer 1

, ACT TMP ( CTAS )

• TMP HIST
• ACT.

, - , , , , ( ), , (, HIST).

INTERVAL PARTITONING ( ).

-- step 1 copy the partition to be moved from ACT table
create table tmp as 
select * from t_act partition FOR (TO_DATE('2016-02-21', 'YYYY-MM-DD'));

-- step 2 allocate partition in HIST table
LOCK TABLE t_hist
PARTITION FOR (TO_DATE('2016-02-21', 'YYYY-MM-DD'))
IN SHARE MODE;


-- step 3 publish the copy in HIST table and DROP the ACT partition
ALTER TABLE t_hist
EXCHANGE PARTITION FOR (TO_DATE('2016-02-21', 'YYYY-MM-DD'))
WITH TABLE tmp
INCLUDING INDEXES;

alter table t_act drop partition FOR (TO_DATE('2016-02-21', 'YYYY-MM-DD')) UPDATE INDEXES;

drop  TABLE t_act;
CREATE TABLE t_act 
 (id number,
 transaction_date DATE not null,
  vc_pad VARCHAR2(100)
  )
 PARTITION BY RANGE (transaction_date)
 INTERVAL (NUMTODSINTERVAL(1,'DAY'))
  (
  PARTITION P_01  VALUES LESS THAN (TO_DATE('2016-02-22', 'YYYY-MM-DD') ),
  PARTITION P_02  VALUES LESS THAN (TO_DATE('2016-02-23', 'YYYY-MM-DD') )
  );

ALTER TABLE t_act ADD CONSTRAINT t_act_pk PRIMARY KEY (id);  
create index  t_act_ix1 on t_act(transaction_date); 

drop TABLE t_hist;
CREATE TABLE t_hist 
 (id number,
 transaction_date DATE not null,
  vc_pad VARCHAR2(100)
  )
 PARTITION BY RANGE (transaction_date)
 INTERVAL (NUMTODSINTERVAL(1,'DAY'))
  (
  PARTITION P_01  VALUES LESS THAN (TO_DATE('2016-02-20', 'YYYY-MM-DD') ),
  PARTITION P_02  VALUES LESS THAN (TO_DATE('2016-02-21', 'YYYY-MM-DD') )
  );

insert into t_act
select rownum, TO_DATE('2016-02-21', 'YYYY-MM-DD') ,'TEST' from dual connect by level <= 100000;
commit;