exchange partition and transport tablespace with unique constraints

Here's the situation. We have a large database (150G) with key tables that are range partitioned by month (with tablespaces for each table partition) . The goal is to have a rolling year online and be able to restore any old partition when needed. The other goal is to have the database online 24 X 7... or as close to that as we can.  The plan is to exchange the partitions for a given month with non-partitioned "holder" tables. Then, export (ala transport) the exchanged partitioned tablespaces. We can then backup the meta file and db files containing the partitioned tablespaces and reverse this process and get the partition back online. Or, before you comment about all the tablespace and partition names that will pile up, we can drop the partition and set up a new database for handling the archives.
  This is all well and good... until you consider the constraints, especially the primary key and unique constraints. You can set up your tables so that you can exchange all you want with all the constraints you like... but you're never going to be able to export a transportable tablespace that have FK, PK, or unique constraints. I'm not sure how all this pointing works... but, Oracle knows they are pointing to something that is outside the transportable tablespaces (not self contained).
  This is fine, you say, disable the constraints, exchange, and transport, voila.. re-enable and you're fine. The catch is... I've got some big tables, so disabling/re-enabling constraints that have indexes supporting them is not possible. So, the only solution I see is to create non-unique indexes for all of my primary keys and unique constraints. This lets me disable without losing the index and then I must do an "enable novalidate" and throw in a "rely" when dealing with all of the constraints on these tables.

  The conclusion: For large partitioned tables that "archive" partitions... unique indexes cannot be used.

 Am I missing something here? Is there another way? I had always thought unique indexes were useful. Am I stuck in a non-unique world.... aaaahhh!

john. Received on Fri Apr 05 2002 - 18:44:34 CST