Comando TRUNCATE: Nova opção CASCADE para apagar dados de forma instantânea

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

Abraço