Oracle Partition Exchange-archived data

The procedure for swapping partitions is as follows:

1. Create the partition table T1, assuming there are 2 partitions, P1,P2.

2. Create the base table T11 the data that holds the P1 rule.

3. Create the base table T12 the data that holds the P2 rule.

4. Exchange P1 partitions using base table T11 and partition table T1. Put the table T11 data into the P1 partition

5. Use base table T12 and partition table T1P2 partition Exchange. Store the table T12 data to the P2 partition.

----1. One partition exchange in an unpartitioned table and a partitioned table

CREATE TABLE T1

(

SID int NOT NULL primary key,

Sname VARCHAR2 (50)

)

PARTITION by Range (SID)

(PARTITION p1 VALUES less THAN (5000) tablespace test,

PARTITION P2 VALUES less THAN (10000) tablespace test,

PARTITION P3 VALUES less THAN (maxvalue) tablespace test

) tablespace test;

Sql> Select COUNT (*) from T1;

COUNT (*)

----------

0

CREATE TABLE T11

(

SID int NOT NULL primary key,

Sname VARCHAR2 (50)

) tablespace test;

CREATE TABLE T12

(

SID int NOT NULL primary key,

Sname VARCHAR2 (50)

) tablespace test;

CREATE TABLE T13

(

SID int NOT NULL primary key,

Sname VARCHAR2 (50)

) tablespace test;

--Loop Import data

Declare

MAXRECORDS constant int:=4999;

I int: = 1;

Begin

For I in 1..maxrecords loop

INSERT into T11 values (i, ' Ocpyang ');

End Loop;

Dbms_output.put_line (' Successful data entry! ');

Commit

End

/

Declare

MAXRECORDS constant int:=9999;

I int: = 5000;

Begin

For I in 5000..maxrecords loop

INSERT into T12 values (i, ' Ocpyang ');

End Loop;

Dbms_output.put_line (' Successful data entry! ');

Commit

End

/

Declare

MAXRECORDS constant int:=70000;

I int: = 10000;

Begin

For I in 10000..maxrecords loop

INSERT into t13 values (i, ' Ocpyang ');

End Loop;

Dbms_output.put_line (' Successful data entry! ');

Commit

End

/

Commit

Sql> Select COUNT (*) from T11;

COUNT (*)

----------

4999

Sql> Select COUNT (*) from T12;

COUNT (*)

----------

5000

Sql> Select COUNT (*) from t13;

COUNT (*)

----------

60001

--Swap partitions

ALTER TABLE T1 exchange partition P1 with table T11;

Sql> Select COUNT (*) from T11; --base table T11 data is 0

COUNT (*)

----------

0

Sql> Select COUNT (*) from T1 partition (P1); --Partition table P1 partition data base table T11 data

COUNT (*)

----------

4999

ALTER TABLE T1 exchange partition P2 with table T12;

Select COUNT (*) from T12;

Select COUNT (*) from T1 partition (P2);

ALTER TABLE T1 exchange partition P3 with table t13;

Select COUNT (*) from t13;

Select COUNT (*) from T1 partition (p3);