Oracle 12c: Truncate Table Cascade

July 2nd, 2013 | Posted in 12c, Blog | 1 Comment


In this post, we will test the new “Truncate Table Cascade” command in 12c.

When you specify the “Truncate Table Cascade” all child tables will be truncated when they are referenced with a “DELETE ON CASCADE” option.


Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 
Connected as wissem
 
SQL> 
SQL> DROP TABLE Tab_tst4_child PURGE
  2  /
 
Table dropped
SQL> DROP TABLE Tab_tst4  PURGE
  2  /
 
Table dropped
 
SQL> 
SQL> CREATE TABLE Tab_tst4(COL1_ID NUMBER PRIMARY KEY, COL2_NAME      INT)
  2   PARTITION BY RANGE(COL2_NAME) INTERVAL (10)
  3   (PARTITION PART1 VALUES LESS THAN (10));
 
Table created
 
SQL> 
SQL> CREATE TABLE Tab_tst4_child(COL1_ID_FK INT NOT NULL, COL2_NAME INT,
  2   CONSTRAINT COL1_ID1_FK FOREIGN KEY(COL1_ID_FK) REFERENCES Tab_tst4(COL1_ID) on delete CASCADE)
  3   PARTITION BY REFERENCE(COL1_ID1_FK);
 
Table created
 
SQL> 
SQL> INSERT INTO Tab_tst4 VALUES(10, 10);
 
1 row inserted
SQL> INSERT INTO Tab_tst4 VALUES(20, 20);
 
1 row inserted
SQL> INSERT INTO Tab_tst4 VALUES(30, 30);
 
1 row inserted
SQL> INSERT INTO Tab_tst4_child VALUES(10, 12);
 
1 row inserted
SQL> INSERT INTO Tab_tst4_child VALUES(20, 13);
 
1 row inserted
SQL> INSERT INTO Tab_tst4_child VALUES(30, 14);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> TRUNCATE TABLE Tab_tst4 CASCADE;
 
Table truncated
 
SQL> select * from Tab_tst4_child;
 
                             COL1_ID_FK                               COL2_NAME
--------------------------------------- ---------------------------------------
 
SQL> select * from Tab_tst4;
 
   COL1_ID                               COL2_NAME
---------- ---------------------------------------
 
SQL> 

If you specify TRUNCATE TABLE … CASCADE for a child table not having ON DELETE CASCADE on the refetential constraint you will have “ORA-14705: unique or primary keys referenced by enabled foreign keys in table “WISSEM”.”TAB_TST4_CHILD”"

SQL> TRUNCATE TABLE Tab_tst4 CASCADE;
 
TRUNCATE TABLE Tab_tst4 CASCADE
 
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "WISSEM"."TAB_TST4_CHILD"
 
SQL> 

In 11gR2, you will have the following error;

SQL> TRUNCATE TABLE Tab_tst4 CASCADE;
 
TRUNCATE TABLE Tab_tst4 CASCADE
 
ORA-03291: Invalid truncate option - missing STORAGE keyword
 
SQL> 

References ; Doc 12cR1



One Comment to “Oracle 12c: Truncate Table Cascade”


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!
  • Nav: Indeed helpful, to the point article..
  • Koti: Can you please explain all the sections of AWR report in RAC. I mean to ask output produced by awrgrpt.sql...
  • Himanshu: Nice post.can you please comment on this Why RMAN restored the DATA_REORTING datafie to +DATA diskgroup?
  • Enrique: Great post.
  • gabriel: what version of sqldeveloper support plugable database?
  • Kal: Quality – thanks…
  • wissem: Well , like I mentioned in the post, delete service and add service using IP.
  • Kelly: were you able to solve this? Thanks!
  • wissem: Hello, You can buy the book from PakT: http://www.packtpub.com/oracle -data-guard-11gr2-administr...
  • goutham: hi i want to buy this book.could you please let me know how can i buy this book. i am in Malaysia. thanks...
  • cesar Lopez: Hola me gustaria saber tu punto de vista de MYSQl vs ORACLE para una sistema de Nomina de unos 1500...
  • wissem: Check my previous article in English; Oracle 12c: RMAN recover Table http://www.oracle-class.com/?p =2866
  • NILESH: Sir, English please
  • Ramakrishna: I want step by step installation of Hyper-table in windows and also usage of Hyper-table like data...

Oracle-Class Forums Latest Activity