設定時間格式

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 ;

ITPub博客

oracle 交換分區歷史數據歸檔

最新文章