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

  • T Robinson: In regard to the comment “In our case, RMAN didn’t found anything corrupted.” Please notice...
  • iftikhar: if we backup in multiple backupsets and will be command for restoring for more than one backupsets ,
  • Abey: Very helpful article.Thanks a lot. Can we follow this on oracle DG enabled deployment also?
  • Mauricio Daher: Need help configuring SBT for cloud backups. Does not seem to work for 12c. Thanks! RMAN>...
  • Leszek K: *.db_create_file_dest=’+ DATA’ create database command – why do you use full datafile...
  • Prabhakar Kumar: Thanks buddy!!! It really helps to DBAs who are novice to 12c ASM feature.
  • Wubeshet: I am getting the ORA-07445 This morning on the trace file. The following is the symptom of the problem. It...
  • Lakshmi N: Hi Thanks for the post. Just curious, is there a way to restore to a point wher my db is in no archive...
  • Shivakkumar P: This is really very good article. Iam learning Oracle 12c I used this to replicate testing from...
  • Uday: Hi Wissem, Once we apply the level 1 incremental backup to datafile copy it is no longed required. My question...
  • avnish: Can I set up active duplicate database in 11g standard edition (11.2.0.1)
  • Jaya V: hi, am having rman backup. i need to restore on it, from test instance. But the test instance name not same...
  • Jaya: hi, am having rman backup. i need to restore on it, from test instance. But the test instance name not same...
  • wissem: SQL Developer 4.0
  • oracleman consulting: great info thanks for sharing
  • 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

Oracle-Class Forums Latest Activity