mantovam wrote:It's not obvious from your description which is the parent and which the child, but there is an alternative which minimises the work.
We need truncate partition of 3 tables ( table1,table2,table3 ), each partition have 4 list partitions ( part1,part2,part3,part4), have PK -> FK between 2 of this tables, and the plan is use this list partition in cicle:
We can not do this only by command-->alter table table2 truncate partition part1, because will raise the error -->ORA-02266, because is partition:
1-)Delete the partition, in order ( FK after PK ) like: delete from table1 partition (part1); delete from table2 partition (part1). After alter table table2 truncate partition part1.
2-)Disable constraints (FK) run the command--> alter table table2 truncate partition part1. After enable the constraints.
Jonathan Lewis wrote:I've gone back to my notes, and re-run an old test against a 10.2.0.3 database - the thing I was only half-remembering was how to exchange partitions without disabling foreign keys. In your case you need only exchange empty partitions to get to a state that is identical to having truncated.
Create a non-partitioned copy of the parent and child tables
alter table child_table exchange partition with child_clone;
alter table parent_table exchange partiiton with parent_clone;
UPDATE: It's just occurred to me that I haven't tested this for several years - so I may describing a workaround for DROPPING parent/child partitions rather than truncating them.