Exchanging a Partition of an Interval Partitioned Table

Let’s create Interval-Partitioned table and exchange partitions.

Step 1 : Create Interval Partitioned Table

SQL> CREATE TABLE TRAN_ALL
  2  (TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER
  )
PARTITION BY RANGE(DATE_OF_TRANSACTION) INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION P1 VALUES LESS THAN(TO_DATE('01-01-2019','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN(TO_DATE('01-02-2019','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN(TO_DATE('01-03-2019','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN(TO_DATE('01-04-2019','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN(TO_DATE('01-05-2019','DD-MM-YYYY')),
PARTITION P6 VALUES LESS THAN(TO_DATE('01-06-2019','DD-MM-YYYY')))
;

Let’s insert some records in TRAN_ALL table. I have staging table which has some records .

SQL> INSERT INTO TRAN_ALL SELECT * FROM TRANSACTIONS1;

724992 rows created.

SQL> COMMIT;

Commit complete.

Let’s create one table using structure of above table with which we can exchange partition of TRAN_ALL table.

SQL> CREATE TABLE TRAN_FEB_2019 AS SELECT * FROM TRAN_ALL WHERE ROWNUM<1;

Table created.  

Let’s exchange partition :

SQL> ALTER TABLE TRAN_ALL EXCHANGE PARTITION FOR (TO_DATE('01-03-2019','DD-MM-YYYY')) WITH TABLE TRAN_FEB_2019;

Table altered.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp