Partition switching alter table exchange partition on-line table history table exchange

Create table test_part_1 Default to users Tablespace:

create table test_part_1(a number, b number)

partition by range(a)

(

partition p1 values less than (10),

partition p2 values less than (20),

partition p3 values less than (30),

partition p4 values less than (40)

);

Create test_part_1 local index

create index idx_id on test_part_1(a) local tablespace TS_KSZIP_BASE;

--Insert record

insert into test_part_1 values(1,2);

insert into test_part_1 values(11,2);

insert into test_part_1 values(21,2);

insert into test_part_1 values(31,2);

commit;

--View records

select rowid from test_part_1 where a=1;--AAAlz4AAEAAFTUEAAA Inquiry 1

--Create intermediate table

create table test_part_3(a number, b number);

create index idx_id3 on test_part_3(a);--default table space users

--test_part_1 exchanges with intermediate tables

alter table test_part_1 exchange partition p1 with table test_part_3 including indexes with validation; --Target table has data cannot be exchanged, exchange can only be partitioned and non-partitioned table exchange

--Verification

select * from dba_ind_partitions where index_name=upper ('idx_id');--p1's table space becomes users and the state is unusable, no rebuild required

select * from dba_indexes where index_name=upper ('idx_id3');--Tablespace becomes TS_KSZIP_BASE.

select rowid from test_part_3;--AAAlz4AAEAAFTUEAAA Compared with query 1 Visible only changed data dictionary

--Create target partition table test_part_2

create table test_part_2(a number, b number)

partition by range(a)

(

partition p1 values less than (10),

partition p2 values less than (20),

partition p3 values less than (30),

partition p4 values less than (40),

partition p5 values less than (50)

);

create index idx_id2 on test_part_2(a) local tablespace TS_KSZIP_BASE;

alter table test_part_2 exchange partition p1 with table test_part_3 including indexes with validation; --Target table has data that cannot be exchanged, exchange can only be partition non-partition exchange

select * from dba_ind_partitions where index_name=upper ('idx_id2');--index p1 is available, table space is still TS_KSZIP_BASE(because idx_id3 table space is TS_KSZIP_BASE)

select * from dba_indexes where index_name=upper ('idx_id3');--Tablespace is TS_KSZIP_BASE, status is also usable