Re: truncate tables in my tablespace
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