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

  • Mike: Very nice article!! Thanks for sharing!!
  • yathish: I really appreciate if you can send me, if you have tutorial for 11g (11.2.0.1) RAC & ASM (prefer to be...
  • Farrukh Kamal: Fahad, I’m interesting in pursuing remote employment with Pythian. What tips would you suggest...
  • pavankumar: for the error it need to set env correctly http://orcl11gdba.blogspot.in/ 2014/08/asmcmd-08102-no-con...
  • wissem: Post result off; crsctl check has crsctl check crs
  • ron: unable to connect to Cluster Synchronization Service
  • KevinB: Please post or attach the response file
  • nnarimanov: I’d like to clarify the reason why rman restores all datafiles into +DATA. It’s because of...
  • Sergio: Hi, Where is declared the function agent_clean() ? I dont see agent_clean() definition !! regards, Sergio
  • Sikandar: I do salute you brother, This is very………..nti mes helpful and correct documents. I...
  • Karthik Singh: Thanks for sharing a useful article. Looks like you have installed weblogic 10.3.4. Then shall I...
  • Joseph: Paul, Here target is from which database we are taking cloning. and auxilary is the cloning database. Wissem,...
  • sundar: In Doc ID 1472171.1 it says that the snapshot control file should be in a shared location for RAC databases....
  • Taoqir: Hello, This is a wonderfull and very helping material.now I am going to configure Data Guard. Thanks
  • 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!