oracle交换分区转移数据,oracle 分区交换-归档数据

交换分区的操作步骤如下：

1. 创建分区表t1，假设有2个分区，P1，P2.

2. 创建基表t11存放P1规则的数据。

3. 创建基表t12 存放P2规则的数据。

4. 用基表t11和分区表T1的P1分区交换。 把表t11的数据放到到P1分区

5. 用基表t12 和分区表T1p2 分区交换。 把表t12的数据存放到P2分区。

----1.未分区表和分区表中一个分区交换

createtablet1

(

sid intnotnullprimarykey,

sname  varchar2(50)

)

PARTITION BYrange(sid)

( PARTITION p1 VALUESLESS THAN (5000) tablespace test,

PARTITION p2 VALUESLESS THAN (10000) tablespace test,

PARTITION p3  VALUESLESS THAN (maxvalue) tablespace test

) tablespace test;

SQL> selectcount(*)fromt1;

COUNT(*)

----------

0

createtablet11

(

sid intnotnullprimarykey,

sname  varchar2(50)

) tablespace test;

createtablet12

(

sid intnotnullprimarykey,

sname  varchar2(50)

) tablespace test;

createtablet13

(

sid intnotnullprimarykey,

sname  varchar2(50)

) tablespace test;

--循环导入数据

declare

maxrecords constant int:=4999;

i int:=1;

begin

foriin1..maxrecords loop

insertintot11values(i,'ocpyang');

endloop;

dbms_output.put_line(' 成功录入数据！ ');

commit;

end;

/

declare

maxrecords constant int:=9999;

i int:=5000;

begin

foriin5000..maxrecords loop

insertintot12values(i,'ocpyang');

endloop;

dbms_output.put_line(' 成功录入数据！ ');

commit;

end;

/

declare

maxrecords constant int:=70000;

i int:=10000;

begin

foriin10000..maxrecords loop

insertintot13values(i,'ocpyang');

endloop;

dbms_output.put_line(' 成功录入数据！ ');

commit;

end;

/

commit;

SQL> selectcount(*)fromt11;

COUNT(*)

----------

4999

SQL> selectcount(*)fromt12;

COUNT(*)

----------

5000

SQL> selectcount(*)fromt13;

COUNT(*)

----------

60001

--交换分区

altertablet1 exchange partition p1withtablet11;

SQL> selectcount(*)fromt11;--基表t11数据为0

COUNT(*)

----------

0

SQL> selectcount(*)fromt1 partition (p1);--分区表的P1分区数据位基表t11的数据

COUNT(*)

----------

4999

altertablet1 exchange partition p2withtablet12;

selectcount(*)fromt12;

selectcount(*)fromt1 partition (p2);

altertablet1 exchange partition p3withtablet13;

selectcount(*)fromt13;

selectcount(*)fromt1 partition (p3);

-----2.分区表和分区表交换

/*

EXCHANGE PARTITION WITHTABLE的方式不支持分区表与分区表的交换，只能通过中间表中转.

*/

--2.1源表

createtablespace jinrilog

datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\jinrilog01.DBF'

size200M  autoextendonnext20M maxsize unlimited

extent management localautoallocate

segment spacemanagement auto

;

createtablespace jinrilogindex

datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\jinrilogindex01.DBF'

size200M  autoextendonnext20M maxsize unlimited

extent management localautoallocate

segment spacemanagement auto

;

createtablet1

(

sid intnotnull,

sname  varchar2(50) notnull,

createtime datedefaultsysdatenotnull

)

PARTITION BYrange(createtime)

(

PARTITION p1 VALUESLESS THAN ('2013-06-01 00:00:00') tablespace jinrilog,

PARTITION p2 VALUESLESS THAN ('2013-07-01 00:00:00') tablespace jinrilog,

PARTITION p3 VALUESLESS THAN ('2013-08-01 00:00:00') tablespace jinrilog,

PARTITION p4  VALUESLESS THAN (maxvalue) tablespace jinrilog

) tablespace jinrilog;

createuniqueindexun_t1_01ont1(sid,createtime)

tablespace jinrilogindex

local;

altertablet1addconstraintpk_t1primarykey(sid,createtime);

createindexindex_t1_01

ont1 (snameasc)

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

foriin1..maxrecords loop

insertintot1values(i,'ocpyang','2013-06-11 00:00:00');

endloop;

dbms_output.put_line(' 成功录入数据！ ');

commit;

end;

/

declare

maxrecords constant int:=2000;

i int:=1;

begin

foriin1..maxrecords loop

insertintot1values(i,'ocpyang','2013-07-11 00:00:00');

endloop;

dbms_output.put_line(' 成功录入数据！ ');

commit;

end;

/

declare

maxrecords constant int:=3000;

i int:=1;

begin

foriin1..maxrecords loop

insertintot1values(i,'ocpyang','2013-08-11 00:00:00');

endloop;

dbms_output.put_line(' 成功录入数据！ ');

commit;

end;

/

SQL> selectcount(*)fromt1;

COUNT(*)

----------

6000

SQL> selectcount(*)fromt1 partition(p1) ;

COUNT(*)

----------

0

SQL>

SQL> selectcount(*)fromt1 partition(p2) ;

COUNT(*)

----------

1000

SQL> selectcount(*)fromt1 partition(p3) ;

COUNT(*)

----------

2000

SQL> selectcount(*)fromt1 partition(p4) ;

COUNT(*)

----------

3000

---查看表数据分区情况

selectutp.table_name,utp.partition_name,utp.tablespace_namefromuser_tab_partitions utp

whereutp.table_name='T1';

--查看分区索引分布情况

col index_name fora20

col partition_name fora20

col tablespace_name fora20

col status fora10

selectindex_name,nullpartition_name,tablespace_name,status

fromuser_indexes

wheretable_name='T1'

andpartitioned='NO'

union

selectindex_name,partition_name,tablespace_name,statusfromuser_ind_partitions

whereindex_namein

(

selectindex_namefromuser_indexes

wheretable_name='T1'

)

orderby1,2,3

;

--2.2 和中间表交换数据

createtablet11

(

sid intnotnull,

sname  varchar2(50)  notnull,

createtime datedefaultsysdatenotnull

)tablespace jason;

selectcount(*)fromt11;

altertablet1 exchange partition p2withtablet11;

--查看无效的索引并重建

col index_name fora20

col partition_name fora20

col tablespace_name fora20

col status fora10

selectindex_name,nullpartition_name,status

fromuser_indexes

wheretable_name='T1'

andpartitioned='NO'

union

selectindex_name,partition_name,statusfromuser_ind_partitions

whereindex_namein

(

selectindex_namefromuser_indexes

wheretable_name='T1'

)

orderby1,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

alterindexINDEX_T1_01  rebuild partition INDEX_SNAME_02;

alterindexUN_T1_01  rebuild partition P2;

col index_name fora20

col partition_name fora20

col tablespace_name fora20

col status fora10

selectindex_name,nullpartition_name,status

fromuser_indexes

wheretable_name='T1'

andpartitioned='NO'

union

selectindex_name,partition_name,statusfromuser_ind_partitions

whereindex_namein

(

selectindex_namefromuser_indexes

wheretable_name='T1'

)

orderby1,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

selectcount(*)fromt1 partition (p2);

COUNT(*)

----------

0

selectcount(*)fromt11;

COUNT(*)

---------

1000

--确定数据是否已经切换到新的表空间

SELECTTABLESPACE_NAME

FROMUSER_TAB_PARTITIONS

WHERETABLE_NAME='T1'ANDPARTITION_NAME='P2';

TABLESPACE_NAME

------------------------------

JASON

---2.3中间表和归档表再次交换数据

createtablespace archive01

datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\archive01.DBF'

size200M  autoextendonnext20M maxsize unlimited

extent management localautoallocate

segment spacemanagement auto

;

createtablespace archive02

datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\archive02.DBF'

size200M  autoextendonnext20M maxsize unlimited

extent management localautoallocate

segment spacemanagement auto

;

createtablet2

(

sid intnotnull,

sname  varchar2(50)  notnull,

createtime datedefaultsysdatenotnull

)

PARTITION BYrange(createtime)

(

PARTITION p1 VALUESLESS THAN ('2013-06-01 00:00:00') tablespace archive01,

PARTITION p2 VALUESLESS THAN ('2013-07-01 00:00:00') tablespace archive01,

PARTITION p3 VALUESLESS THAN ('2013-08-01 00:00:00') tablespace archive01,

PARTITION p4  VALUESLESS THAN (maxvalue) tablespace archive01

) tablespace archive01;

createuniqueindexun_t2_01ont2(sid,createtime)

tablespace archive02

local;

altertablet2addconstraintpk_t2primarykey(sid,createtime);

selectup.table_name,up.partition_name,up.tablespace_namefromuser_tab_partitions up

whereup.table_name='T2';

--查看分区索引分布情况

col index_name fora20

col partition_name fora20

col tablespace_name fora20

col status fora10

selectindex_name,nullpartition_name,tablespace_name,status

fromuser_indexes

wheretable_name='T2'

andpartitioned='NO'

union

selectindex_name,partition_name,tablespace_name,statusfromuser_ind_partitions

whereindex_namein

(

selectindex_namefromuser_indexes

wheretable_name='T2'

)

orderby1,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

selectcount(*)fromt2;

COUNT(*)

---------

0

--交换数据

altertablet2 exchange partition p2withtablet11 ;

selectcount(*)fromt2;

selectcount(*)fromt11;