Oracle EBS Oracle : 12c Truncate Cascade

Oracle introduced TRUNCATE Casade feature in Oracle 12c.

What is use of that ?

For example

SQL>create table table1 (

  id           number,

  desc  varchar2(50),

  constraint table1_pk primary key (id)

);

/ 

SQL>create table table2 (

  id             number,

  t1_id          number,

  desc           varchar2(50),

  constraint table2_pk primary key (id),

  constraint table2_t1_fk foreign key (t1_id) references table1 (id) on delete cascade

);

/

 SQL>create table table3 (

  id             number,

  t2_id          number,

  desc           varchar2(50),

  constraint table3_pk primary key (id),

  constraint table3_t2_fk foreign key (t2_id) references table2 (id) on delete cascade

);

  

SQL>begin

  insert into table1 values (1, 'test1 ONE');

  insert into table2 values (1, 1, 'test2 ONE');

  insert into table2 values (2, null, 'test2 TWO');

  insert into table3 values (1, 1, 'test3 ONE');

  insert into table3 values (2, null, 'test3 TWO');

  commit;

end;

/

 

SQL>truncate table tab1e1 cascade;

 

The TRUNCATE TABLE ... CASCADE command succeeds and recursively truncates all the dependent tables.

if we issue the command

SQL>select (select count(*) from tab1e1) as t1_count,

       (select count(*) from table2) as t2_count,

       (select count(*) from table3) as t3_count

from   dual;

 

T1_COUNT   T2_COUNT   T3_COUNT

---------- ---------- ----------

 0            0         0

 

it will delete all rows on dependent tables .i.e table1,table2,table3