Cascading Truncate

In pre-Oracle 12c , we cannot truncate table , if the table has referential integrity with its child table.
First we would need to truncate table the child table , and then the parent . If we attempt to truncate parent table , then we would get "ORA-02266" . 

However , in Oracle 12c , we could truncate the table , if the referential integrity is set to "on delete cascade" . The cascade of the truncate can go up to multiple levels ( children , grand children and so on .. )  . 

Needless to mention that this needs to be done with extreme caution , as "TRUNCATE" cannot rolled back in Oracle ( In SQL Server , it could be rolled back if placed in a transaction . That beats the definition of a DDL .. In any case , that is a subject of a different post) . 

Comments welcome.

Here is an example in Oracle 11g.

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> Create table emp as select * from scott.emp ;
Table created.
 
SQL> Create table dept  as select * from scott.dept ;
Table created.

SQL> alter table dept add constraint pk_dept primary key ( deptno) ;
Table altered.

SQL> alter table emp add constraint pk_emp primary key ( empno) ;
Table altered.

SQL> alter table emp add constraint fk_emp_dept foreign key ( deptno )  references dept(deptno) on delete cascade;
 
Table altered.

SQL> truncate table dept cascade;
truncate table dept cascade
                    *
ERROR at line 1:
ORA-03291: Invalid truncate option - missing STORAGE keyword


SQL> truncate table dept ;
truncate table dept
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Here is from Oracle 12c.

SQL> select * from v$version ;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                                0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

SQL> drop table emp purge ;
Table dropped.

SQL> drop table dept purge ;
Table dropped.

SQL> Create table emp as select * from scott.emp ;
Table created.

SQL> Create table dept  as select * from scott.dept ;
Table created.

SQL> alter table dept add constraint pk_dept primary key ( deptno) ;
Table altered.

SQL> alter table emp add constraint pk_emp primary key ( empno) ;
Table altered.

SQL> alter table emp add constraint fk_emp_dept foreign key ( deptno )  references dept(deptno) on delete cascade;
Table altered.

SQL> truncate table dept cascade;
Table truncated.