Partitioning - Expert Oracle Database Architecture - page 627

We'll do the same to the new data to be loaded. We'll create and load a table that structurally looks like the
existing partitioned table (but that is not itself partitioned):
EODA@ORA12CR1> create table fy_2016 ( timestamp date, id int );
Table created.
EODA@ORA12CR1> insert into fy_2016
2 select to_date('31-dec-2016','dd-mon-yyyy')-mod(rownum,360), rownum
3 from dual connect by level <= 70000
4 /
70000 rows created.
EODA@ORA12CR1> create index fy_2016_idx on fy_2016(id) nologging;
Index created.
We'll turn the current full partition into an empty partition and create a full table with the FY_2014 data in it. Also,
we've completed all of the work necessary to have the FY_2016 data ready to go. This would have involved verifying
the data, transforming it—whatever complex tasks we need to undertake to get it ready.
Now we're ready to update the live data using an exchange partition:
EODA@ORA12CR1> alter table partitioned
2 exchange partition fy_2014
3 with table fy_2014
4 including indexes
5 without validation
6 /
Table altered.
EODA@ORA12CR1> alter table partitioned drop partition fy_2014;
Table altered.
This is all we need to do to age the old data out. We turned the partition into a full table and the empty table into a
partition. This was a simple data dictionary update. No large amount of I/O took place—it just happened. We can now
export that FY_2014 table (perhaps using a transportable tablespace) out of our database for archival purposes. We
could reattach it quickly if we ever needed to.
Next, we want to slide in the new data:
EODA@ORA12CR1> alter table partitioned
2 add partition fy_2016
3 values less than ( to_date('01-jan-2017','dd-mon-yyyy') )
4 /
Table altered.
EODA@ORA12CR1> alter table partitioned
2 exchange partition fy_2016
3 with table fy_2016
4 including indexes
5 without validation
6 /
Table altered.