A recent posting on the OTN database forum (which I didn’t answer correctly on my first attempt) raised the problem of truncating partitions when there are referential integrity constraints in place. Let’s start with a demonstration of the problem:
create table parent ( id number, v1 varchar2(10), padding varchar2(100), constraint p_pk primary key(id) using index local ) partition by range(id) ( partition p1000 values less than (1000), partition p2000 values less than (2000), partition p3000 values less than (3000), partition p4000 values less than (4000), partition p5000 values less than (5000) ) ; create table child ( id_p number, seq_c number, v1 varchar2(10), padding varchar2(100), constraint c_fk_p foreign key (id_p) references parent, constraint c_pk primary key (id_p, seq_c) using index local ) partition by range(id_p) ( partition p1000 values less than (1000), partition p2000 values less than (2000), partition p3000 values less than (3000), partition p4000 values less than (4000), partition p5000 values less than (5000) ) ; insert into parent select rownum - 1, rpad(rownum - 1,10), rpad('x',100) from all_objects where rownum <= 4000 ; insert into child select id, val, v1, padding from parent, ( select 1 val from dual union all select 2 val from dual union all select 3 val from dual union all select 4 val from dual ) ; -- gather some stats on the two tables. alter table child truncate partition p4000; alter table parent truncate partition p4000;
The problem is that you can’t truncate the parent partition because Oracle thinks there may be related data in the child partition – despite the fact that the internal code could be written to see that the partition’s data segment was “new”. The output from the two truncate commands is as follows:
-- the call for the child works Table truncated. alter table parent truncate partition p4000 * ERROR at line 1: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You can’t truncate the parent partition unless you disable the foreign key constraint – and it might take quite a long time to re-enable it since we usually only worry about partitioning for large objects.
There is a slightly surprising workaround that might be good enough. If you set the foreign key constraint to novalidate, rather than disable, you can tell Oracle to exchange partitions without checking the validity of the foreign key constraint. So:
create table parent_ex ( id number, v1 varchar2(10), padding varchar2(100), constraint px_pk primary key(id) ) ; create table child_ex ( id_p number, seq_c number, v1 varchar2(10), padding varchar2(100), constraint cx_fk_px foreign key (id_p) references parent_ex, constraint cx_pk primary key (id_p, seq_c) ) ; alter table child modify constraint c_fk_p rely enable novalidate ; alter table child_ex modify constraint cx_fk_px rely enable novalidate ; prompt ============================================================ prompt We can now exchange the child and parent partitions we want. prompt ============================================================ alter table child exchange partition p3000 with table child_ex including indexes without validation ; alter table parent exchange partition p3000 with table parent_ex including indexes without validation ;
Having done the two exchanges, we have two empty partitions in the partitioned table and we can truncate the resulting parent_ex and child_ex tables. If we wanted we could also modify the referential integrity constraint back to validate mode – although this would take some time, of course, and we might just decide that it wasn’t necessary because (we believe) all the old data would be correct, and we may only be worried about making sure that new data will be checked.
The downside to this method is that we need to keep (or recreate) a pair of tables in the right tablespace so that we can do the exchange when we need to; but that doesn’t seem to be a terrible price to pay for the ability to “truncate” a single partition without disabling referential integrity constraints.