12c - Truncate Cascade

No Oracle Database 12c, existe uma nova opção para o comando TRUNCATE, o CASCADE.

Ao contrário de DELETE, o comando TRUNCATE TABLE só altera a High Water Mark da tabela, por isso ele quase não gera UNDO e é instantâneo.

 
SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 14 12:51:43 2013
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
 
SQL> truncate table scott.dept;
truncate table scott.dept
                     *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
 


Buscando a constraint que faz referência a tabela:

 
SQL> select owner,constraint_name,table_name,delete_rule from dba_constraints where (r_owner,r_constraint_name) = (select owner,constraint_name from dba_constraints where owner='SCOTT' and table_name = 'DEPT' and constraint_type='P');
 
OWNER       CONSTRAINT_NAME     TABLE_NAME           DELETE_RULE
---------- -------------------- -------------------- --------------------
SCOTT       FK_DEPTNO           EMP                  NO ACTION
 


Verificando dos dados da tabela:

SQL> select * from scott.emp;
 
     EMPNO ENAME      JOB        MGR   HIREDATE        SAL        COMM  DEP
---------- ---------- --------- ----- ---------  ---------- ----------  ---
      7369 SMITH      CLERK      7902  17-DEC-80        800             20
      7499 ALLEN      SALES      7698  20-FEB-81       1600    300      30
      7521 WARD       SALESMAN   7698  22-FEB-81       1250    500      30
      7566 JONES      MANAGER    7839  02-APR-81       2975             20
      7654 MARTIN     SALESMAN   7698  28-SEP-81       1250   1400      30
      7698 BLAKE      MANAGER    7839  01-MAY-81       2850             30

      7782 CLARK      MANAGER    7839  09-JUN-81       2450             10

      7788 SCOTT      ANALYST    7566  09-DEC-82       3000             20

      7839 KING       PRESIDENT        17-NOV-81       5000             10

      7844 TURNER     SALESMAN   7698  08-SEP-81       1500             30

      7876 ADAMS      CLERK      7788  12-JAN-83       1100             20

      7900 JAMES      CLERK      7698  03-DEC-81        950             30

      7902 FORD       ANALYST    7566  03-DEC-81       3000             20

      7934 MILLER     CLERK      7782  23-JAN-82       1300             10

 
14 rows selected.
 
SQL>


Utilizando o CASCADE:

SQL> truncate table scott.dept cascade;
truncate table scott.dept cascade
                     *
ERROR at line 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "SCOTT"."EMP"


O erro acima ocorreu porque a constraint não é do tipo ON DELETE
CASCADE.

Iremos mudar o tipo da constraint:

SQL> ALTER TABLE SCOTT.EMP DROP
  CONSTRAINT FK_DEPTNO;  
 
Table altered.
 
SQL> ALTER TABLE SCOTT.EMP ADD (
  CONSTRAINT FK_DEPTNO 
  FOREIGN KEY (DEPTNO) 
  REFERENCES SCOTT.DEPT (DEPTNO) on delete cascade);
 
Table altered.


Executando novamente o comando:

SQL> truncate table scott.dept cascade;
 
Table truncated.


Os dados foram apagados com sucesso:

SQL> select * from scott.dept;

no rows selected

SQL> select * from scott.emp;

no rows selected

Abraço

Alex Zaballa