Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

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

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: