oracle 交換分割槽歷史資料歸檔

設定時間格式

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

//建立表 並分割槽
 create table acc_item_cur
(acc_item number,
 acc_item_desc varchar2(20),
 acc_date date,
area_code varchar2(6)
  )
  partition by range (acc_date)
subpartition by list (area_code)
(partition d1 values less than (TO_DATE('2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
  (subpartition d1_con1 values('ASIA','AUST'),
   subpartition d1_con2 values('AMER'),
   subpartition d1_con3 values('AFRICA'),
   subpartition d1_con4 values('EUROPE')),
   partition d2 values less than (TO_DATE('2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
  (subpartition d2_con1 values('ASIA','AUST'),
   subpartition d2_con2 values('AMER'),
   subpartition d2_con3 values('AFRICA'),
   subpartition d2_con4 values('EUROPE')),
   partition d3 values less than (TO_DATE('2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
  (subpartition d3_con1 values('ASIA','AUST'),
   subpartition d3_con2 values('AMER'),
   subpartition d3_con3 values('AFRICA'),
   subpartition d3_con4 values('EUROPE'))
   );

新增本地索引，並將其設定為主鍵。
SQL>   create unique index pk_acc_item_cur on acc_item_cur(acc_item,acc_date,area_code) local;

Index created.

SQL> alter table acc_item_cur add primary key (acc_item,acc_date,area_code) using index

pk_acc_item_cur;

Table altered.

SQL> create sequence seq_acc_item cache 10000 noorder;

Sequence created.

SQL>    declare
     vdate date;
   begin
     vdate:=to_date('2012-01-01','yyyy-mm-dd');
     for i in 1.. 4000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'ASIA');
     end loop
     commit;
     for i in 1.. 5000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'AMER');
     end loop
     commit;

     for i in 1.. 5000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'AFRICA');
     end loop
     commit;

    for i in 1.. 5000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'EUROPE');
     end loop
     commit;
   end;    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19

  20   21   22   23
 24  /

PL/SQL procedure successfully completed.

SQL>    declare
     vdate date;
   begin
     vdate:=to_date('2012-02-05','yyyy-mm-dd');
     for i in 1.. 4000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'ASIA');
     end loop
     commit;
     for i in 1.. 5000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'AMER');
     end loop
     commit;

     for i in 1.. 5000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'AFRICA');
     end loop
     commit;

    for i in 1.. 5000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'EUROPE');
     end loop
     commit;
   end;    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19

  20   21   22   23
 24  /

PL/SQL procedure successfully completed.

SQL>    declare
     vdate date;
   begin
     vdate:=to_date('2012-03-05','yyyy-mm-dd');
     for i in 1.. 4000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'ASIA');
     end loop
     commit;
     for i in 1.. 5000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'AMER');
     end loop
     commit;

     for i in 1.. 5000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'AFRICA');
     end loop
     commit;

    for i in 1.. 5000 loop
       insert into acc_item_cur values(seq_acc_item.nextval,to_char(i),vdate,'EUROPE');
     end loop
     commit;
   end;    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19

  20   21   22   23
 24  /

PL/SQL procedure successfully completed.

建立中間表

SQL>  create table acc_item_ex
  2  (acc_item number,
  3  acc_item_desc varchar2(20),
  4  acc_date date,
  5  area_code varchar2(6)
  6  )
  7  partition by  list (area_code)
  8  (partition d1_con1 values('ASIA','AUST'),
  9  partition d1_con2 values('AMER'),
 10   partition d1_con3 values('AFRICA'),
 11   partition d1_con4 values('EUROPE'));

Table created.

//建立索引

 create unique index pk_acc_item_ex on acc_item_ex(acc_item,acc_date,area_code) local;
 alter table acc_item_ex add primary key (acc_item,acc_date,area_code) using index pk_acc_item_ex;

建立歷史表

SQL> create table acc_item_his
(acc_item number,
 acc_item_desc varchar2(20),
 acc_date date,
area_code varchar2(6)
  )
  partition by range (acc_date)
subpartition by list (area_code)
(partition d1 values less than (TO_DATE('2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
  (subpartition d1_con1 values('ASIA','AUST'),
   subpartition d1_con2 values('AMER'),
   subpartition d1_con3 values('AFRICA'),
   subpartition d1_con4 values('EUROPE')),
   partition d2 values less than (TO_DATE('2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
  (subpartition d2_con1 values('ASIA','AUST'),
   subpartition d2_con2 values('AMER'),
   subpartition d2_con3 values('AFRICA'),
   subpartition d2_con4 values('EUROPE')),
   partition d3 values less than (TO_DATE('2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
  (subpartition d3_con1 values('ASIA','AUST'),
   subpartition d3_con2 values('AMER'),
   subpartition d3_con3 values('AFRICA'),
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20     subpartition d3_con4 values('EUROPE'))
   ); 21   22   23   24
/

Table created.
SQL>   create unique index pk_acc_item_cur on acc_item_his(acc_item,acc_date,area_code) local;

Index created.

SQL> alter table acc_item_his add primary key (acc_item,acc_date,area_code) using index

pk_acc_item_his;

交換分割槽
SQL> alter table acc_item_cur exchange partition d1 with table acc_item_ex including index ;

執行失敗，去除including index  執行OK

  alter table acc_item_cur exchange partition d1 with table acc_item_ex OK
SQL>  alter table acc_item_his exchange partition d1 with table acc_item_ex including index without validation ;

執行失敗 ，去除including index  執行OK

  alter table acc_item_his exchange partition d1 with table acc_item_ex without validation ;