Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Truncate a partition with relationship

mantovamMay 31 2012 — edited Jun 1 2012
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 ?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 29 2012
Added on May 31 2012
5 comments
1,315 views