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

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

原文链接：https://www.f2er.com/oracle/210797.html

猜你在找的Oracle相关文章