Re: truncate tables in my tablespace

From: <fitzjarrell_at_cox.net>
Date: Fri, 18 Jan 2008 09:18:50 -0800 (PST)
Message-ID: <e36cfbaf-d958-4a81-9430-4424769b2b8d@u10g2000prn.googlegroups.com>


On Jan 18, 5:07 am, gazzag <gar..._at_jamms.org> wrote:
> On 18 Jan, 10:53, burrell.j..._at_yahoo.com wrote:
>
> > Thanks you all for the very thorough answers- One question remains
> > tho. My intention is the thro all the data away so would delete from
> > tablename have the same restriction with constraints as truncate?
> > J- Hide quoted text -
>
> > - Show quoted text -
>
> Yes, DELETE will also have issues with foreign keys.  Plus, TRUNCATE
> will be faster.
>
> HTH
>
> -g

It will if the deletes are not performed in proper order:

SQL> --
SQL> -- Add primary key to emp
SQL> --
SQL> alter table emp

  2 add constraint emp_pk
  3 primary key(empno)
  4 using index tablespace indx;

Table altered.

SQL>
SQL> --
SQL> -- Add primary key to dept
SQL> --
SQL> alter table dept

  2 add constraint dept_pk
  3 primary key(deptno)
  4 using index tablespace indx;

Table altered.

SQL>
SQL> --
SQL> -- Add foreign key to emp
SQL> -- referencing dept
SQL> --
SQL> alter table emp

  2 add constraint emp_dept_fk
  3 foreign key (deptno)
  4 references dept;

Table altered.

SQL>
SQL> --
SQL> -- Try to delete from dept
SQL> --
SQL> delete from dept;

delete from dept
*
ERROR at line 1:
ORA-02292: integrity constraint (BING.EMP_DEPT_FK) violated - child record
found
SQL>
SQL> --
SQL> -- Looks like we have an error
SQL> --
SQL> -- Delete dependent records first
SQL> --
SQL> delete from emp;

14 rows deleted.

SQL>
SQL> --
SQL> -- Now delete from dept
SQL> --
SQL> delete from dept;

4 rows deleted.

SQL> Additionally, if such errors do occur simply running the same script again will delete the parents to now-deleted child records:

SQL>
SQL> select 'delete from '||table_name||';'   2 from user_tables
  3 order by table_name;

'DELETEFROM'||TABLE_NAME||';'



delete from BONUS;
delete from DEPT;
delete from DUMMY;
delete from EMP;
delete from SALGRADE;

SQL> spool del_all_tabs.sql
SQL> / 'DELETEFROM'||TABLE_NAME||';'



delete from BONUS;
delete from DEPT;
delete from DUMMY;
delete from EMP;
delete from SALGRADE;
SQL> spool off
SQL> @del_all_tabs
SQL> delete from BONUS;

0 rows deleted.

SQL> delete from DEPT;
delete from DEPT
*
ERROR at line 1:
ORA-02292: integrity constraint (BING.EMP_DEPT_FK) violated - child record
found

SQL> delete from DUMMY;

1 row deleted.

SQL> delete from EMP;

14 rows deleted.

SQL> delete from SALGRADE;

0 rows deleted.

SQL>
SQL>
SQL> @del_all_tabs
SQL>
SQL> delete from BONUS;

0 rows deleted.

SQL> delete from DEPT;

4 rows deleted.

SQL> delete from DUMMY;

0 rows deleted.

SQL> delete from EMP;

0 rows deleted.

SQL> delete from SALGRADE;

0 rows deleted.

SQL> TRUNCATE won't behave this way, as it's DDL, not DML:

SQL> spool trunc_all_tbls.sql
SQL> /
truncate table BONUS;
truncate table DEPT;
truncate table DUMMY;
truncate table EMP;
truncate table SALGRADE;

SQL> spool off
SQL> @trunc_all_tbls
SQL> truncate table BONUS;

Table truncated.

SQL> truncate table DEPT;
truncate table DEPT

               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> truncate table DUMMY;

Table truncated.

SQL> truncate table EMP;

Table truncated.

SQL> truncate table SALGRADE;

Table truncated.

SQL>
SQL> @trunc_all_tbls
SQL> truncate table BONUS;

Table truncated.

SQL> truncate table DEPT;
truncate table DEPT

               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> truncate table DUMMY;

Table truncated.

SQL> truncate table EMP;

Table truncated.

SQL> truncate table SALGRADE;

Table truncated.

SQL> One can 'get around' the foreign key constraint issue with delete, although it requires multiple passes of a plain-vanilla delete script. It's a better practice, thought, to disable such constraints before performing a delete (and it's absolutely necessary before attempting a mass truncate operation).

David Fitzjarrell Received on Fri Jan 18 2008 - 11:18:50 CST

Original text of this message