Oracle用分区表分区交换做历史数据迁移 - 数据库编程

an_2013_12 tablespace tan_2013_12, partition ind_tan_2014_1 tablespace tan_2014_1, partition ind_tan_2014_2 tablespace tan_2014_2, partition ind_tan_2014_3 tablespace tan_2014_3, partition ind_tan_2014_4 tablespace tan_2014_4, partition ind_tan_2014_5 tablespace tan_2014_5, partition ind_tan_2014_6 tablespace tan_2014_6, partition ind_tan_2014_7 tablespace tan_2014_7, partition ind_tan_2014_8 tablespace tan_2014_8 ); begin for i in 1.. 10000 loop if( mod(i,12)+1 <=8) then insert into tan values(i,'tan'||i,to_date('2014-'||(mod(i,12)+1)||'-01','yyyy-mm-dd')); else insert into tan values(i,'tan'||i,to_date('2013-'||(mod(i,12)+1)||'-01','yyyy-mm-dd')); end if; end loop; commit; end; / SQL> select count(*) from tan partition(tan_2013_12) ; COUNT(*) ---------- 833 SQL> select count(*) from tan partition(tan_2013_9) ; COUNT(*) ---------- 833 SQL> select count(*) from tan partition(tan_2014_8) ; COUNT(*) ---------- 833 SQL> select count(*) from tan partition(tan_2014_1) ; COUNT(*) ---------- 833 SQL> select partition_name,tablespace_name from dba_segments where segment_name in ('TAN','IND_TAN'); PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TAN_2014_8 TAN_2014_8 TAN_2014_7 TAN_2014_7 TAN_2014_6 TAN_2014_6 TAN_2014_5 TAN_2014_5 TAN_2014_4 TAN_2014_4 TAN_2014_3 TAN_2014_3 TAN_2014_2 TAN_2014_2 TAN_2014_1 TAN_2014_1 TAN_2013_9 TAN_2013_9 TAN_2013_12 TAN_2013_12 TAN_2013_11 TAN_2013_11 TAN_2013_10 TAN_2013_10 IND_TAN_2014_8 TAN_2014_8 IND_TAN_2014_7 TAN_2014_7 IND_TAN_2014_6 TAN_2014_6 IND_TAN_2014_5 TAN_2014_5 IND_TAN_2014_4 TAN_2014_4 IND_TAN_2014_3 TAN_2014_3 IND_TAN_2014_2 TAN_2014_2 IND_TAN_2014_1 TAN_2014_1 IND_TAN_2013_9 TAN_2013_9 IND_TAN_2013_12 TAN_2013_12 IND_TAN_2013_11 TAN_2013_11 IND_TAN_2013_10 TAN_2013_10 24 rows selected.

OLAP库环境准备

create tablespace tan_2013_7 datafile size 5m autoextend on;

create tablespace tan_2013_8 datafile size 5m autoextend on;
 create table tan
(t_id number(10),
t_name varchar2(100),
t_date date )
partition by range(t_date)
(partition tan_2013_7 values less than(to_date('2013-08-01','yyyy-mm-dd')) tablespace tan_2013_7,
partition tan_2013_8 values less than(to_date('2013-09-01','yyyy-mm-dd')) tablespace tan_2013_8
);

create index ind_tan on tan(t_date) local
(
partition ind_tan_2013_7  tablespace tan_2013_7,
partition ind_tan_2013_8 tablespace tan_2013_8
);


begin 
for i in 1.. 10000 loop
insert into tan values(i,'tan'||i,to_date('2013-'||(mod(i,2)+7)||'-01','yyyy-mm-dd'));
end loop;
commit;
end;
/


SQL> select count(*) from tan partition(tan_2013_8);


  COUNT(*)
----------
      5000


SQL> select count(*) from tan partition(tan_2013_7);


  COUNT(*)
----------
      5000

2.分区交换

现在要做的事是迁移2013年9月份数据。

创建个临时表：
SQL> create table tmp_tan_2013_9 as select * from tan where 1=2;
SQL> create index ind_tmp_tan_2013_9 on tmp_tan_2013_9(t_date);

分区交换。
SQL> alter table tan exchange partition tan_2013_9

with table tmp_tan_2013_9 including indexes with validation;

验证：
SQL> select count(*) from tan partition(tan_2013_9) ;

  COUNT(*)
----------
         0
SQL> select count(*) from tmp_tan_2013_9;
  COUNT(*)
----------
       833
SQL> select partition_name,tablespace_name from dba_segments
     where segment_name in ('TAN','IND_TAN');


PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------

Oracle用分区表分区交换做历史数据迁移(二)

OLAP库环境准备

2.分区交换