Truncate a partition with relationship
Hi gurus
Database 10.2
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:
Example:
part1-->truncate
part2-->data
part3-->data
part4 -->data
part1-->data
part2-->truncate
part3-->data
part4 -->data
We can not do this only by command-->alter table table2 truncate partition part1, because will raise the error -->ORA-02266, because is partition:
Alternatives:
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.
OR
2-)Disable constraints (FK) run the command--> alter table table2 truncate partition part1. After enable the constraints.
Both of alternatives we think that will work, but in the #1 will use undo because the delete, in #2 When enable the constraint the database will do the check of constraint.
What is the better option ?