Shulou(Shulou.com)06/01 Report--

Starting from Oracle8, it provides the function of exchanging from partition, such as the exchange of one partition or sub-partition with a non-partition table, the exchange of hash sub-partition between a hash partition and another table, and so on. For more information, please refer to the official documentation.

Basic syntax: ALTER TABLE...EXCHANGE PARTITION

Experimental environment: 11.2.0.4

Zx@ORCL > select * from v$version BANNER- -Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionPL/SQL Release 11.2.0.4.0-ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0- ProductionNLSRTL Version 11.2.0.4.0-Production

Test Partition Exchange

Create a test table

-- partitioned table zx@ORCL > create table t12 (id number (2), 3 name varchar2 (15)) 4 tablespace tt 5 partition by range (id) 6 (partition p1 values less than (10), 7 partition p2 values less than (20), 8 partition p3 values less than (30)); Table created.-- non-partitioned table zx@ORCL > create table T2 (id number (2), name varchar2 (15)) tablespace users;Table created.-- insert test data zx@ORCL > insert into T1 values (1,'1') 1 row created.zx@ORCL > insert into T1 values (11, '11'); 1 row created.zx@ORCL > insert into T1 values (21,' 21'); 1 row created.zx@ORCL > insert into T2 values (2,'2'); 1 row created.zx@ORCL > commit;Commit complete.zx@ORCL > select * from T1 ID NAME--11 11 11 21 21zx@ORCL > select * from T2 ID NAME--22 USERS-View Table Storage Tablespace-T2 in Tablespace T1 each partition is in the TT tablespace zx@ORCL > col segment_name for a20zx@ORCL > col partition_name for a15zx@ORCL > col tablespace_name for a15zx@ORCL > select segment_name,partition_name,tablespace_name from dba_segments where segment_name in ('T1 records and T2') SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME--T2 USERST1 P3 TTT1 P2 TTT1 P1 TT-- View the extent information of each table zx@ORCL > select SEGMENT_NAME BLOCK_ID,BLOCKS,TABLESPACE_NAME from dba_extents where segment_name='T2' SEGMENT_NAME BLOCK_ID BLOCKS TABLESPACE_NAME--T2 1928 USERSzx@ORCL > select SEGMENT_NAME,PARTITION_NAME,BLOCK_ID,BLOCKS,TABLESPACE_NAME from dba_extents where segment_name='T1' SEGMENT_NAME PARTITION_NAME BLOCK_ID BLOCKS TABLESPACE_NAME--T1 P2 21376 1024 TTT1 P3 22400 1024 TTT1 P1 20352 1024 TT

T1 partition p1 and T2 table swap partition

-- Division zx@ORCL > alter table T1 exchange partition p1 with table T2 * table altered.zx@ORCL > select * from T2; ID NAME--1 1zx@ORCL > select * from T1 ID NAME--2 2 11 11 21 21

You can see that the data in the p1 partition is exchanged in the T2 table, and the data in the T2 table is also stored in the T1 table. Check the tablespace and extent of each table again

-- look at the tablespace zx@ORCL > select segment_name,partition_name,tablespace_name from dba_segments where segment_name in ('T _ 1 'and' T _ 2') SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME--T2 TTT1 P3 TTT1 P2 TTT1 P1 USERS-- View extentzx@ORCL > select SEGMENT_NAME BLOCK_ID,BLOCKS,TABLESPACE_NAME from dba_extents where segment_name='T2' SEGMENT_NAME BLOCK_ID BLOCKS TABLESPACE_NAME--T2 20352 1024 TTzx@ORCL > select SEGMENT_NAME,PARTITION_NAME,BLOCK_ID,BLOCKS,TABLESPACE_NAME from dba_extents where segment_name='T1' SEGMENT_NAME PARTITION_NAME BLOCK_ID BLOCKS TABLESPACE_NAME--T1 P1 192 8 USERST1 P2 21376 1024 TTT1 P3 22400 1024 TT

From the results, we can see that T2 has reached the TT table space, and the P1 partition of T1 has been moved to the users table space, and the P1 partition has also been exchanged with the extent of T2 table. It can be inferred that the data in the actual table has not moved, but the relevant information in the data dictionary has been changed.

Second, take a look at the impact of swap partitions on the indexes of partitioned tables

Create an index in a partitioned table

-- Global index zx@ORCL > create index idx_t1_id on T1 (id); Index created.-- partition index zx@ORCL > create index idx_t1_name on T1 (name) local;Index created.zx@ORCL > select index_name,status from user_indexes where index_name like 'IDX_T1%' INDEX_NAME STATUS -IDX_T1_ID VALIDIDX_T1_NAME N/Azx@ORCL > select index_name Partition_name,status from user_ind_partitions where index_name like 'IDX_T1%' INDEX_NAME PARTITION_NAME STATUS -IDX_T1_NAME P1 USABLEIDX_T1_NAME P2 USABLEIDX_T1_NAME P3 USABLE

Swap partitions to see if it affects the index

Zx@ORCL > alter table T1 exchange partition p1 with table T2 crystal table altered.zx@ORCL > select index_name,status from user_indexes where index_name like 'IDX_T1%' INDEX_NAME STATUS -IDX_T1_NAME N/AIDX_T1_ID UNUSABLEzx@ORCL > select index_name Partition_name,status from user_ind_partitions where index_name like 'IDX_T1%' INDEX_NAME PARTITION_NAME STATUS -IDX_T1_NAME P1 UNUSABLEIDX_T1_NAME P2 USABLEIDX_T1_NAME P3 USABLE

It is seen that the global index IDX_T1_ID is invalid, and so is the partition index corresponding to partition P1, but the partitions of other partitions are not affected.

Add a UPDATE INDEXES clause when exchanging partitions

Zx@ORCL > alter index idx_t1_id rebuild;Index altered.zx@ORCL > alter index idx_t1_name rebuild partition p1bot Index altered.zx@ORCL > select index_name,status from user_indexes where index_name like 'IDX_T1%' INDEX_NAME STATUS -IDX_T1_NAME N/AIDX_T1_ID VALIDzx@ORCL > select index_name Partition_name,status from user_ind_partitions where index_name like 'IDX_T1%' INDEX_NAME PARTITION_NAME STATUS -IDX_T1_NAME P1 USABLEIDX_T1_NAME P2 USABLEIDX_T1_NAME P3 USABLEzx@ORCL > alter table T1 exchange partition p1 with table T2 update indexes Table altered.zx@ORCL > select index_name,status from user_indexes where index_name like 'IDX_T1%' INDEX_NAME STATUS -IDX_T1_NAME N/AIDX_T1_ID VALIDzx@ORCL > select index_name Partition_name,status from user_ind_partitions where index_name like 'IDX_T1%' INDEX_NAME PARTITION_NAME STATUS -IDX_T1_NAME P1 UNUSABLEIDX_T1_NAME P2 USABLEIDX_T1_NAME P3 USABLE

You can see that the global index is not affected, but the partitioned index is still invalid.

For more information, please refer to the official documentation: http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1107555

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.