Oracle partition exchange-archive data

The procedure for Oracle partition exchange is as follows: 1. Create Partition Table t1. Assume that there are two partitions, P1 and P2.2. create a base table t11 to store data of the P1 rule. 3. Create a base table t12

The procedure for Oracle partition exchange is as follows: 1. Create Partition Table t1. Assume that there are two partitions, P1 and P2.2. create a base table t11 to store data of the P1 rule. 3. Create a base table t12

The procedure for Oracle partition switching is as follows:

1. Create Partition Table t1. Assume that there are two partitions, P1 and P2.
2. Create a base table t11 to store data of the P1 rule.
3. Create a base table t12 to store P2 rule data.
4. Use the P1 partition exchange between the base table t11 and the Partition Table T1. Put the data in Table t11 to the P1 partition.
5. Use the base table t12 and the Partition Table T1p2 for partition exchange. Store the data in Table t12 to the P2 partition.

---- 1. A partition exchange between a non-partitioned 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;

-- Cyclically 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 ('data entered successfully! ');
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 ('data entered successfully! ');
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 ('data entered successfully! ');
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; -- the base table t11 data is 0

COUNT (*)
----------
0

SQL> select count (*) from t1 partition (p1); -- partition Table P1 partition data bit 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 );

,