Oracle Database 12c, now TRUNCATE statement is enhanced with  CASCADE option. This is pretty useful, that you need not worry about truncating all “child” tables before truncating the parent. With the CASCADE option, Oracle will truncate all tables with a foreign key constraint to the table being truncated. The foreign key must be ENABLED and defined with “ON DELETE CASCADE” clause. There is no set limit to the recursive level of cascaded truncate. All child, grand child, great grandchild, etc tables will be truncated.

Oracle12c also extended the same feature to truncating a partition. Now, ALTER TABLE … TRUNCATE PARTITION can include the CASCADE option as well. Specify CASCADE to truncate the corresponding partition(s) or subpartition(s) in all reference-partitioned child tables of table being truncated.



%d bloggers like this: