oracle分区交换技术

yinian

　　交换分区的操作步骤如下：
　　1. 创建分区表t1，假设有2个分区，P1，P2.
　　2. 创建基表t11存放P1规则的数据。
　　3. 创建基表t12 存放P2规则的数据。
　　4. 用基表t11和分区表T1的P1分区交换。 把表t11的数据放到到P1分区
　　5. 用基表t12 和分区表T1p2 分区交换。 把表t12的数据存放到P2分区。
　　----1.未分区表和分区表中一个分区交换
　　create table t1
　　(
　　sid int not null primary key,
　　sname  varchar2(50)
　　)
　　PARTITION BY range(sid)
　　( PARTITION p1 VALUES LESS THAN (5000) tablespace test,
　　PARTITION p2 VALUES LESS THAN (10000) tablespace test,
　　PARTITION p3  VALUES LESS THAN (maxvalue) tablespace test
　　) tablespace test;
　　SQL> select count(*) from t1;
　　COUNT(*)
　　----------
　　0
　　create table t11
　　(
　　sid int not null primary key,
　　sname  varchar2(50)
　　) tablespace test;
　　create table t12
　　(
　　sid int not null primary key,
　　sname  varchar2(50)
　　) tablespace test;
　　create table t13
　　(
　　sid int not null primary key,
　　sname  varchar2(50)
　　) tablespace test;
　　--循环导入数据
　　declare
　　maxrecords constant int:=4999;
　　i int :=1;
　　begin
　　for i in 1..maxrecords loop
　　insert into t11 values(i,'ocpyang');
　　end loop;
　　dbms_output.put_line(' 成功录入数据！ ');
　　commit;
　　end;
　　/
　　declare
　　maxrecords constant int:=9999;
　　i int :=5000;
　　begin
　　for i in 5000..maxrecords loop
　　insert into t12 values(i,'ocpyang');
　　end loop;
　　dbms_output.put_line(' 成功录入数据！ ');
　　commit;
　　end;
　　/
　　declare
　　maxrecords constant int:=70000;
　　i int :=10000;
　　begin
　　for i in 10000..maxrecords loop
　　insert into t13 values(i,'ocpyang');
　　end loop;
　　dbms_output.put_line(' 成功录入数据！ ');
　　commit;
　　end;
　　/
　　commit;
　　SQL> select count(*) from t11;
　　COUNT(*)
　　----------
　　4999
　　SQL> select count(*) from t12;
　　COUNT(*)
　　----------
　　5000
　　SQL> select count(*) from t13;
　　COUNT(*)
　　----------
　　60001
　　--交换分区
　　alter table t1 exchange partition p1 with table t11;
　　SQL> select count(*) from t11;   --基表t11数据为0
　　COUNT(*)
　　----------
　　0
　　SQL> select count(*) from t1 partition (p1);  --分区表的P1分区数据位基表t11的数据
　　COUNT(*)
　　----------
　　4999
　　alter table t1 exchange partition p2 with table t12;
　　select count(*) from t12;
　　select count(*) from t1 partition (p2);
　　alter table t1 exchange partition p3 with table t13;
　　select count(*) from t13;
　　select count(*) from t1 partition (p3);
　　-----2.分区表和分区表交换
　　/*
　　EXCHANGE PARTITION WITH TABLE的方式不支持分区表与分区表的交换，只能通过中间表中转.
　　*/
　　--2.1源表
　　create tablespace jinrilog
　　datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\jinrilog01.DBF'
　　size 200M  autoextend on next 20M maxsize unlimited
　　extent management local autoallocate
　　segment space management auto
　　;
　　create tablespace jinrilogindex
　　datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\jinrilogindex01.DBF'
　　size 200M  autoextend on next 20M maxsize unlimited
　　extent management local autoallocate
　　segment space management auto
　　;
　　create table t1
　　(
　　sid int not null ,
　　sname  varchar2(50) not null,
　　createtime date default sysdate   not null
　　)
　　PARTITION BY range(createtime)
　　(
　　PARTITION p1 VALUES LESS THAN ('2013-06-01 00:00:00') tablespace jinrilog,
　　PARTITION p2 VALUES LESS THAN ('2013-07-01 00:00:00') tablespace jinrilog,
　　PARTITION p3 VALUES LESS THAN ('2013-08-01 00:00:00') tablespace jinrilog,
　　PARTITION p4  VALUES LESS THAN (maxvalue) tablespace jinrilog
　　) tablespace jinrilog;
　　create unique index un_t1_01 on t1(sid,createtime)
　　tablespace jinrilogindex
　　local;
　　alter table t1 add constraint pk_t1 primary key(sid,createtime);
　　create index index_t1_01
　　on t1 (sname  asc)
　　tablespace jinrilogindex
　　local
　　(
　　partition index_sname_01 tablespace jinrilogindex,
　　partition index_sname_02 tablespace jinrilogindex,
　　partition index_sname_03 tablespace jinrilogindex,
　　partition index_sname_04 tablespace jinrilogindex
　　);
　　--循环导入数据
　　declare
　　maxrecords constant int:=1000;
　　i int :=1;
　　begin
　　for i in 1..maxrecords loop
　　insert into t1 values(i,'ocpyang','2013-06-11 00:00:00');
　　end loop;
　　dbms_output.put_line(' 成功录入数据！ ');
　　commit;
　　end;
　　/
　　declare
　　maxrecords constant int:=2000;
　　i int :=1;
　　begin
　　for i in 1..maxrecords loop
　　insert into t1 values(i,'ocpyang','2013-07-11 00:00:00');
　　end loop;
　　dbms_output.put_line(' 成功录入数据！ ');
　　commit;
　　end;
　　/
　　declare
　　maxrecords constant int:=3000;
　　i int :=1;
　　begin
　　for i in 1..maxrecords loop
　　insert into t1 values(i,'ocpyang','2013-08-11 00:00:00');
　　end loop;
　　dbms_output.put_line(' 成功录入数据！ ');
　　commit;
　　end;
　　/
　　SQL> select count(*) from t1;
　　COUNT(*)
　　----------
　　6000
　　SQL> select count(*) from  t1 partition(p1) ;
　　COUNT(*)
　　----------
　　0
　　SQL>
　　SQL> select count(*) from  t1 partition(p2) ;
　　COUNT(*)
　　----------
　　1000
　　SQL> select count(*) from  t1 partition(p3) ;
　　COUNT(*)
　　----------
　　2000
　　SQL> select count(*) from  t1 partition(p4) ;
　　COUNT(*)
　　----------
　　3000
　　---查看表数据分区情况
　　select utp.table_name,utp.partition_name,utp.tablespace_name from user_tab_partitions utp
　　where utp.table_name='T1';
　　--查看分区索引分布情况
　　col index_name for a20
　　col partition_name for a20
　　col tablespace_name for a20
　　col status for a10
　　select index_name,null partition_name,tablespace_name,status
　　from user_indexes
　　where table_name='T1'
　　and partitioned='NO'
　　union
　　select index_name,partition_name,tablespace_name,status from user_ind_partitions
　　where index_name in
　　(
　　select index_name from user_indexes
　　where table_name='T1'
　　)
　　order by 1,2,3
　　;
　　--2.2 和中间表交换数据
　　create table t11
　　(
　　sid int not null ,
　　sname  varchar2(50)  not null,
　　createtime date default sysdate   not null
　　)tablespace jason;
　　select count(*) from t11;
　　alter table t1 exchange partition p2 with table t11;
　　--查看无效的索引并重建
　　col index_name for a20
　　col partition_name for a20
　　col tablespace_name for a20
　　col status for a10
　　select index_name,null partition_name,status
　　from user_indexes
　　where table_name='T1'
　　and partitioned='NO'
　　union
　　select index_name,partition_name,status from user_ind_partitions
　　where index_name in
　　(
　　select index_name from user_indexes
　　where table_name='T1'
　　)
　　order by 1,2,3
　　;
　　INDEX_NAME                     PARTITION_NAME                 STATUS
　　------------------------------ ------------------------------ --------
　　INDEX_T1_01                    INDEX_SNAME_01                 USABLE
　　INDEX_T1_01                    INDEX_SNAME_02                 UNUSABLE
　　INDEX_T1_01                    INDEX_SNAME_03                 USABLE
　　INDEX_T1_01                    INDEX_SNAME_04                 USABLE
　　UN_T1_01                       P1                             USABLE
　　UN_T1_01                       P2                             UNUSABLE
　　UN_T1_01                       P3                             USABLE
　　UN_T1_01                       P4                             USABLE
　　alter index INDEX_T1_01  rebuild partition INDEX_SNAME_02;
　　alter index UN_T1_01  rebuild partition P2;
　　col index_name for a20
　　col partition_name for a20
　　col tablespace_name for a20
　　col status for a10
　　select index_name,null partition_name,status
　　from user_indexes
　　where table_name='T1'
　　and partitioned='NO'
　　union
　　select index_name,partition_name,status from user_ind_partitions
　　where index_name in
　　(
　　select index_name from user_indexes
　　where table_name='T1'
　　)
　　order by 1,2,3
　　;
　　INDEX_NAME                     PARTITION_NAME                 STATUS
　　------------------------------ ------------------------------ --------
　　INDEX_T1_01                    INDEX_SNAME_01                 USABLE
　　INDEX_T1_01                    INDEX_SNAME_02                 USABLE
　　INDEX_T1_01                    INDEX_SNAME_03                 USABLE
　　INDEX_T1_01                    INDEX_SNAME_04                 USABLE
　　UN_T1_01                       P1                             USABLE
　　UN_T1_01                       P2                             USABLE
　　UN_T1_01                       P3                             USABLE
　　UN_T1_01                       P4                             USABLE
　　select count(*) from t1 partition (p2);
　　COUNT(*)
　　----------
　　0
　　select count(*) from t11;
　　COUNT(*)
　　---------
　　1000
　　--确定数据是否已经切换到新的表空间
　　SELECT TABLESPACE_NAME
　　FROM USER_TAB_PARTITIONS
　　WHERE TABLE_NAME='T1' AND PARTITION_NAME='P2';
　　TABLESPACE_NAME
　　------------------------------
　　JASON
　　---2.3中间表和归档表再次交换数据
　　create tablespace archive01
　　datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\archive01.DBF'
　　size 200M  autoextend on next 20M maxsize unlimited
　　extent management local autoallocate
　　segment space management auto
　　;
　　create tablespace archive02
　　datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\archive02.DBF'
　　size 200M  autoextend on next 20M maxsize unlimited
　　extent management local autoallocate
　　segment space management auto
　　;
　　create table t2
　　(
　　sid int not null ,
　　sname  varchar2(50)  not null,
　　createtime date default sysdate   not null
　　)
　　PARTITION BY range(createtime)
　　(
　　PARTITION p1 VALUES LESS THAN ('2013-06-01 00:00:00') tablespace archive01,
　　PARTITION p2 VALUES LESS THAN ('2013-07-01 00:00:00') tablespace archive01,
　　PARTITION p3 VALUES LESS THAN ('2013-08-01 00:00:00') tablespace archive01,
　　PARTITION p4  VALUES LESS THAN (maxvalue) tablespace archive01
　　) tablespace archive01;
　　create unique index un_t2_01 on t2(sid,createtime)
　　tablespace archive02
　　local;
　　alter table t2 add constraint pk_t2 primary key(sid,createtime);
　　select up.table_name,up.partition_name,up.tablespace_name from user_tab_partitions up
　　where up.table_name='T2';
　　--查看分区索引分布情况
　　col index_name for a20
　　col partition_name for a20
　　col tablespace_name for a20
　　col status for a10
　　select index_name,null partition_name,tablespace_name,status
　　from user_indexes
　　where table_name='T2'
　　and partitioned='NO'
　　union
　　select index_name,partition_name,tablespace_name,status from user_ind_partitions
　　where index_name in
　　(
　　select index_name from user_indexes
　　where table_name='T2'
　　)
　　order by 1,2,3
　　;
　　INDEX_NAME           PARTITION_NAME       TABLESPACE_NAME      STATUS
　　-------------------- -------------------- -------------------- ----------
　　UN_T2_01             P1                   ARCHIVE02            USABLE
　　UN_T2_01             P2                   ARCHIVE02            USABLE
　　UN_T2_01             P3                   ARCHIVE02            USABLE
　　UN_T2_01             P4                   ARCHIVE02            USABLE
　　select count(*) from t2;
　　COUNT(*)
　　---------
　　0
　　--交换数据
　　alter table t2 exchange partition p2 with table t11 ;
　　select count(*) from t2;
　　select count(*) from t11;
　　以上内容转自http://blog.csdn.net/yangzhawen/article/details/8768943