Table Partition Purging

Posted on Animesh KumarPosted in Uncategorized

sql> create table source_partitioned
( a number, b varchar2(1), c date)
partition by range (c)
( partition p1 values less than (date ‘2017-01-01’)
, partition pmax values less than (maxvalue)
);
sql> create table target_partitioned
( a number, b varchar2(1), c date)
partition by range (c)
( partition p1 values less than (date ‘2017-01-01’)
, partition pmax values less than (maxvalue)
);
sql> create table partition_holder ( a number, b varchar2(1) , c date );
sql> insert into source_partitioned values ( 1, 1, date ‘2017-01-01’);
sql> insert into source_partitioned values ( 2, 2, sysdate);
sql> alter table source_partitioned exchange partition p1 with table partition_holder;
sql> alter table target_partitioned exchange partition p1 with table partition_holder;
sql> select * from source_partitioned;

A B C
———- – ——————-
2 2 2017-11-28 10:43:37
sql> select * from target_partitioned;

A B C
———- – ——————-
1 1 1998-01-01 00:00:00
sql> alter table source_partitioned exchange partition pmax with table partition_holder;
sql> alter table target_partitioned exchange partition pmax with table partition_holder;
sql> select * from source_partitioned;
sql> select * from target_partitioned;

A B C
———- – ——————-
1 1 2016-01-01 00:00:00
2 2 2016-11-28 14:38:22

18 thoughts on “Table Partition Purging”

Leave a Reply

Your email address will not be published. Required fields are marked *

4 + 3 =