Oracle 12c new features: Truncate table CASCADE

In the previous releases, there wasn’t a direct option provided to truncate a master table while it is referred to by the child tables and child records exist. 

The TRUNCATE TABLE with CASCADE option in 12c truncates the records in the master table and automatically initiates recursive truncate on child tables too, subject to foreign key reference as DELETE ON CASCADE. There is no CAP on the number of recursive levels as it will apply to all child, grand child, and great grandchild etc.

This enhancement gets rid of the prerequisite to truncate all child records before truncating a master table. 

The new CASCADE clause can also be applied on table partitions and subpartitions etc..

SQL> TRUNCATE TABLE <table_name> CASCADE;

SQL> TRUNCATE TABLE <table_name> PARTITION <partition_name> CASCADE;

An ORA-14705 error will be thrown if no ON DELETE CASCADE option is defined with the foreign keys of the child tables.