Thursday, May 14, 2009

I have used exchange partition to archive old data from an existing partition table. As we have a lot of very large partition tables which store data for past years, we exchange older partitions which don't need to be part of the existing table to a new table and then export it and save it on to a tape device as archive data. This way we are able to store the data out of the database and keep it on tapes for any future use. We can import this data whenever we need. We see that exchange partition is widely used in data warehousing environments to keep the tables in a manageable size, achieve adequate performance apart from data archiving. As using exchange partition we can restore this data back into the old table whenever we need it.We can use exchange partition to convert a non partitioned table into a partitioned table also.Syntax and example:

SQL> !oerr ora 1429114291, 00000, "cannot EXCHANGE a composite partition with a non-partitioned table"// *Cause: A composite partition can only be exchanged with a partitioned// table.// *Action: Ensure that the table being exchanged is partitioned or that// that the partition being exchanged is non-composite.

This should that we cannot exchange partition with a normal table like we did the last time, now lets create the correct table:

SQL> drop table DATA_2006;Table dropped.

We need to create a table with the partition scheme as the subpartition scheme of the main/big table, to be able to exchange its partition with a new table:

We can restore the data back into the main table also. We can exchange the this newly created table with the empty partition in the big/main table back, incase we ever need the data back into the main table. Hence if we archive data by exchange partition we can restore them back into the main table if we ever need it. Another thing is if we might need to restore we should not drop the empty partitions as its easier to restore the data, else we would need to do a split partition to create the partitions again.

Note: When we do exchange partition the local indexes become invalid, hence we would need to rebuild the same. If we provide the update global indexes, the global indexes don't become invalid and hence no need to rebuild them.