Truncate Cascade – 12c

Por Alex Zaballa ,
Postado em janeiro 2014

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      SALESMAN  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

Alex Zaballa é DBA e trabalha com Oracle há 14 anos, especialista em Banco de Dados Oracle e formado em Análise de Sistemas. Com sólidos conhecimentos em Banco de Dados e Sistemas operacionais, é certificado OCM Database 11G, bem como outras 110 certificações em produtos da Oracle. Trabalha desde 2007 para a Jupiter em Angola, alocado em um projeto no Ministério das Finanças.