Oracle Exchange partition Example

This article presents a simple method for partitioning an existing table using the EXCHANGE PARTITION syntax.
EXCHANGE PARTITION is also an easy and fast way to copy data from NON Partitioned table to a Partitioned one. Both tables should have the same definition (Columns orders, names and types).

DROP TABLE PARTI_TABLE_1;
DROP TABLE PARTI_TABLE_2;
CREATE TABLE PARTI_TABLE_1
—- the destinationTable
(
CREA_MONTH VARCHAR2(6) ,
CREA_DAY VARCHAR2(8) ,
CREA_DATE DATE ,
DESC VARCHAR2(200) ,
ID NUMBER
)
partition by list (CREA_MONTH)
( partition PARTI_TABLE_201010 values (‘201010’) tablespace USERS )

;

CREATE TABLE PARTI_TABLE_2
—- the Source Table
(

CREA_MONTH VARCHAR2(6) ,
CREA_DAY VARCHAR2(8) ,
CREA_DATE DATE ,
DESC VARCHAR2(200) ,
ID NUMBER
)
tablespace USERS NOLOGGING

;

INSERT INTO PARTI_TABLE_2 (CREA_MONTH,CREA_DAY,CREA_DATE,DESC,ID) VALUES (‘201010’, ‘20101001’, to_date(‘20101001′,’YYYYMMDD’), ‘ABC’, 1);
COMMIT;

ALTER TABLE
—destination table
PARTI_TABLE_1
EXCHANGE PARTITION PARTI_TABLE_201010
WITH TABLE
—-Source data table
PARTI_TABLE_2
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;

SELECT * FROM PARTI_TABLE_1;

Nice reading,
Wissem