Here’s a dirty trick for those of you who are having problems with “partition0-wise” referential integrity on partitioned tables.
If you’ve created a pair of partitioned tables, parent and child say, with referential integrity declared between them, then you’ve probably discovered that you have to set the foreign key constraint on the child table to novalidate if you want to use “partition exchange loading” (as Oracle now calls it) to get data into the child table.
If you’ve got to the point where you decide to drop a pair of old partitions, you may have tried the following:
SQL> alter table child drop partition p2000; Table altered. SQL> alter table parent drop partition p2000; alter table parent drop partition p2000 * ERROR at line 1: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Because they’re your tables you know that you’ve defined them so that data in parent partition(N) and child partition(N) are perfectly synchronised - but Oracle doesn’t know this, so you can’t drop the parent partition unless you actually disable the foreign key constraint. And if the tables are large, you may find it’s rather expensive to re-enable the constraint after the drop.
It’s easy to be caught out whilst testing this, as you can drop the parent partition if it’s never held any data – so a quick “create tables, drop partitions” test will give you a spurious success if you didn’t bother to insert any data into the tables. However, this observation supplies a clue to a cunning trick. You can keep the foreign key constraint enabled with the novalidate option set and do the following:
create table parent_clone as select * from parent where 1 = 0; alter table parent_clone add constraint pc_pk primary key(id); -- modify as necessary alter table parent exchange partition p2000 with table parent_clone including indexes without validation; alter table parent drop partition p2000
You can swap the full partition with a “never used” table, and then drop the resulting empty partition.
Footnote: 11g is expected to include a special ‘ref partitioning’ feature to deal with this specific issue. [Update: but it doesn't apply if your child table is partitioned on the parent primary key and you've copied the key down - which is what I'd hope to see in most (if not all) cases anyway.]
Footnote 2: Bear in mind that, for the duration of the partition maintenance, any queries that would normally be optimised to do a partition-wise join will see the two tables as having different numbers of partitions, which means the partition-wise join feature will be disabled – with dire consequences on your workload and performance.
So make sure that you don’t let anyone run the wrong queries while you’re doing this type of partition maintenance.