oracle 多个分区交换,Oracle分区交换

从Oracle8开始，提供了从分区交换的功能，如一个分区或子分区与一个非分区表交换、一个hash分区与另一个表的hash子分区交换等等，详细的交换方式可以参考官方文档。

基本语法：ALTER TABLE...EXCHANGE PARTITION

实验环境：11.2.0.4

zx@ORCL>select*fromv$version;

BANNER

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

OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction

PL/sqlRelease11.2.0.4.0-Production

CORE11.2.0.4.0Production

TNSforLinux:Version11.2.0.4.0-Production

NLSRTLVersion11.2.0.4.0-Production

一、测试分区交换

创建测试表

--分区表

zx@ORCL>createtablet1

2(idnumber(2),3namevarchar2(15))

4tablespacett

5partitionbyrange(id)

6(partitionp1valueslessthan(10),7partitionp2valueslessthan(20),8partitionp3valueslessthan(30));

Tablecreated.

--非分区表

zx@ORCL>createtablet2(idnumber(2),namevarchar2(15))tablespaceusers;

Tablecreated.

--插入测试数据

zx@ORCL>insertintot1values(1,'1');

1rowcreated.

zx@ORCL>insertintot1values(11,'11');

1rowcreated.

zx@ORCL>insertintot1values(21,'21');

1rowcreated.

zx@ORCL>insertintot2values(2,'2');

1rowcreated.

zx@ORCL>commit;

Commitcomplete.

zx@ORCL>select*fromt1;

IDNAME

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

11

1111

2121

zx@ORCL>select*fromt2;

IDNAME

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

22

--查看表存储表空间

--t2在USERS表空间，t1各个分区都在TT表空间

zx@ORCL>colsegment_namefora20

zx@ORCL>colpartition_namefora15

zx@ORCL>coltablespace_namefora15

zx@ORCL>selectsegment_name,partition_name,tablespace_namefromdba_segmentswheresegment_namein('T1','T2');

SEGMENT_NAMEPARTITION_NAMetaBLESPACE_NAME

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

T2USERS

T1P3TT

T1P2TT

T1P1TT

--查看各表的extent信息

zx@ORCL>selectSEGMENT_NAME,BLOCK_ID,BLOCKS,TABLESPACE_NAMEfromdba_extentswheresegment_name='T2';

SEGMENT_NAMEBLOCK_IDBLOCKSTABLESPACE_NAME

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

T21928USERS

zx@ORCL>selectSEGMENT_NAME,PARTITION_NAME,TABLESPACE_NAMEfromdba_extentswheresegment_name='T1';

SEGMENT_NAMEPARTITION_NAMEBLOCK_IDBLOCKSTABLESPACE_NAME

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

T1P2213761024TT

T1P3224001024TT

T1P1203521024TT

t1分区p1与t2表交换分区

--分区

zx@ORCL>altertablet1exchangepartitionp1withtablet2;

Tablealtered.

zx@ORCL>select*fromt2;

IDNAME

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

11

zx@ORCL>select*fromt1;

IDNAME

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

22

1111

2121

可以看到p1分区里的数据交换到了t2表里，而t2表里里的数据也存储到了t1表中。再次查看各表所在的表空间和extent

--查看表空间

zx@ORCL>selectsegment_name,'T2');

SEGMENT_NAMEPARTITION_NAMetaBLESPACE_NAME

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

T2TT

T1P3TT

T1P2TT

T1P1USERS

--查看extent

zx@ORCL>selectSEGMENT_NAME,TABLESPACE_NAMEfromdba_extentswheresegment_name='T2';

SEGMENT_NAMEBLOCK_IDBLOCKSTABLESPACE_NAME

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

T2203521024TT

zx@ORCL>selectSEGMENT_NAME,TABLESPACE_NAMEfromdba_extentswheresegment_name='T1';

SEGMENT_NAMEPARTITION_NAMEBLOCK_IDBLOCKSTABLESPACE_NAME

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

T1P11928USERS

T1P2213761024TT

T1P3224001024TT

从结果看到T2已经到了TT表空间，而T1的P1分区移动到了USERS表空间，而且P1分区与T2表的extent也做了交换，可以推断实际表里的数据没有移动位置，只是把数据字典里的相关信息做了更换。

二、再看看交换分区对于分区表的索引的影响

在分区表中创建索引

--全局索引

zx@ORCL>createindexidx_t1_idont1(id);

Indexcreated.

--分区索引

zx@ORCL>createindexidx_t1_nameont1(name)local;

Indexcreated.

zx@ORCL>selectindex_name,statusfromuser_indexeswhereindex_namelike'IDX_T1%';

INDEX_NAMESTATUS

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

IDX_T1_IDVALID

IDX_T1_NAMEN/A

zx@ORCL>selectindex_name,statusfromuser_ind_partitionswhereindex_namelike'IDX_T1%';

INDEX_NAMEPARTITION_NAMESTATUS

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

IDX_T1_NAMEP1USABLE

IDX_T1_NAMEP2USABLE

IDX_T1_NAMEP3USABLE

交换分区查看是否对索引有影响

zx@ORCL>altertablet1exchangepartitionp1withtablet2;

Tablealtered.

zx@ORCL>selectindex_name,statusfromuser_indexeswhereindex_namelike'IDX_T1%';

INDEX_NAMESTATUS

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

IDX_T1_NAMEN/A

IDX_T1_IDUNUSABLE

zx@ORCL>selectindex_name,statusfromuser_ind_partitionswhereindex_namelike'IDX_T1%';

INDEX_NAMEPARTITION_NAMESTATUS

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

IDX_T1_NAMEP1UNUSABLE

IDX_T1_NAMEP2USABLE

IDX_T1_NAMEP3USABLE

看到全局索引IDX_T1_ID失效了，分区P1对应的分区索引也失效了，但其他分区的分区没有受到影响

交换分区时加入 UPDATE INDEXES子句

zx@ORCL>alterindexidx_t1_idrebuild;

Indexaltered.

zx@ORCL>alterindexidx_t1_namerebuildpartitionp1;

Indexaltered.

zx@ORCL>selectindex_name,statusfromuser_indexeswhereindex_namelike'IDX_T1%';

INDEX_NAMESTATUS

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

IDX_T1_NAMEN/A

IDX_T1_IDVALID

zx@ORCL>selectindex_name,statusfromuser_ind_partitionswhereindex_namelike'IDX_T1%';

INDEX_NAMEPARTITION_NAMESTATUS

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

IDX_T1_NAMEP1USABLE

IDX_T1_NAMEP2USABLE

IDX_T1_NAMEP3USABLE

zx@ORCL>altertablet1exchangepartitionp1withtablet2updateindexes;

Tablealtered.

zx@ORCL>selectindex_name,statusfromuser_ind_partitionswhereindex_namelike'IDX_T1%';

INDEX_NAMEPARTITION_NAMESTATUS

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

IDX_T1_NAMEP1UNUSABLE

IDX_T1_NAMEP2USABLE

IDX_T1_NAMEP3USABLE

可以看到全局索引没有受影响，但是分区索引仍然失效。

总结

以上是编程之家为你收集整理的Oracle分区交换全部内容，希望文章能够帮你解决Oracle分区交换所遇到的程序开发问题。

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。