Oracle Partition Exchange-archived data

Source: Internet
Author: User
Tags commit constant count

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);

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.