Andrew Fraser DBA

schema moves by the magic of partition exchange

Here’s an example of how to use partition exchange to move partitions or even entire unpartitioned tables from one schema to another. Is mean to be very fast and generate very little redo. Even more so if the partitions and tables are kept in the same tablespace.

Process for doing partition exchange is like this:

— first create the archive table, empty initially:

create table arch_owner.mytable .... [full create table spec goes in here, including partition clauses, but leave out the primary key/index ]

— give arch_owner (or alternatively whichever user runs this job) the required privileges

grant select, alter on live_owner.mytable to arch_owner ;

— create an empty temporary table, used later in the partition exchange

create table arch_owner.temp_table as select * from live_owner.mytable where 1=2 ;

— exchange the live partition with the temporary table

alter table live_owner.mytable exchange partition year2001 with table arch_owner.temp_table ;

— exchange that onwards to the archived table

alter table arch_owner.mytable exchange partition year2001 with table arch_owner.temp_table ;

— at the very end of the process, clean up the temporary table, add in any required primary keys or other indexes, and gather optimizer stats (=analyze)

drop table arch_owner.temp_table ;
alter table arch_owner.mytable add primary key (aud_id) using index tablespace ts_index1 ;
exec dbms_stats.gather_schema_stats('ARCH_OWNER', estimate_percent=>99.99, cascade => TRUE, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1')

> On a similar vein, is there an elegant way of copying the current data from the live_owner.mytable table to the arch_owner.mytable table? About 15Gb of data would normally go across via our scripts, but unfortunately the tables aren’t partitioned to let us do something along the lines of your last suggestion …

1) Yes, can do it in essentially the same way as before (h/t Pythian Blog):

— give arch_owner the required privileges on the live table:

grant select, alter on live_owner.mytable to arch_owner ;

— create an empty table and index in arch_owner

create table df_arch_owner.mytable .. [full create table spec here]
create index arch_owner.mytable_index1 on arch_owner.mytable (to_date(plan_date,'YYYY-MM-DD') ) tablespace ts_index1 ;

— also create a temporary table with a single dummy partition

create table arch_owner.temp_table
partition by range ( userid )
( partition dummy values less than ( maxvalue ) )
as select * from arch_owner.mytable where 1=2 ;

— again with an index (locally):

create index arch_owner.temp_index1 on arch_owner.temp_table (to_date(plan_date,'YYYY-MM-DD') ) tablespace ts_index1 local ;

— swap the live table and the temporary table with each other:

alter table arch_owner.temp_table exchange partition dummy with table live_owner.mytable
including indexes without validation ;

— then swap the temporary table and the arch_owner table with each other:

alter table arch_owner.temp_table exchange partition dummy with table arch_owner.mytable
including indexes without validation ;

— optimizer stats for both schemas should be re-gathered at the overall completion of the archiving work, and temporary tables dropped.

2) Or alternative method – but probably not so good because it doesn’t strictly move from one schema to the other, just renames:

— login as live_owner

conn live_owner/password

— rename the old table to have arch_ in front of its name

rename mytable to arch_mytable ;

— rename the old index to have arch_ in front of its name

alter index mytable_index1 rename to arch_mytable_index1 ;

— create a synonym in arch_owner that points to the arch_ table.

create synonym arch_owner.arch_mytable for live_owner.arch_mytable ;

— and also grant arch_owner privileges on the arch_ table

grant select on live_owner.arch_mytable to arch_owner ;

— create a new empty table in live_owner, complete with indexes, triggers, grants, and so on:

create table live_owner.mytable ....
create index mytable_index1 on live_owner.mytable ....
create trigger live_owner.del_mytable ....
grant select, insert, ....

— optionally can move the arch tables from one tablespace to the other (although I don’t see how that could be worth the substantial time and effort that it takes):

alter table arch_mytable move tablespace ts_arch_data ;
alter index arch_mytable_index1 rebuild tablespace ts_arch_index ;

— optimizer stats for both schemas should be re-gathered at the overall completion of the archiving work.

Changing Tablespaces in Partition Exchange

If you need objects to be in specific tablespaces, you should explicitly state that tablespace name, otherwise you can expect the users default tablespace will be used instead. That applies to all operations that alter indexes and tables – including exchange partition, enable constraint, create constraint, create index, alter index rebuild, create table, alter table add partition, and so on.

For partition exchanges, it is a bit more complex than that, because exchanged partitions take their tablespace with them during the exchange.

So imagine an initial setup where the live year2001 partition is in ts_data1, and the other two objects to be used are in tablespaces “X” and “Z”:

After we exchange “live year 2001” with “temp table” their tablespaces swap:

Then exchange “archive year 2001” with “temp table”, same thing happens:

Then we drop the “temp table”:

Now, assuming we want tablespace “X” to be ts_data1, checking back to the initial setup shows that that tablespace was the one defined by the temp table. So it is important to explicitly specify that tablespace, using code like:

— create an empty temporary table, used later in the partition exchange

create table arch_owner.temp_table tablespace ts_data1
as select * from live_owner.mytable where 1=2 ;

Also, assuming we want the “archive year2001” partition to be in ts_arch_data, we have a problem – it has ended up in ts_data1. There is no way to prevent that at the time, instead it has to be moved at the end, using:

alter table arch_owner.owner.mytable move tablespace ts_arch_data ;

That table move is unfortunately slow and generates redo.