Re: Deleting all data from tables

I didn't read the first part of this thread, so apologies if I am repeating previous comments.

Your solution here needs some refinement. You only need to disable constraints of type 'R' (referential) - if you disable all the constraints as you are doing then the
indexes for Primary Key constraints will also be dropped and may be rebuilt in ways you do not like when you re-enable them.

Your approach depends on logging on
to Oracle many times - each connect
introduces another window of opportunity for something to go wrong.

I suggest using a PL/SQL prodecure
roughly like:

cursor loop

    select all relevant table_name, constraint_name     into PL/SQL table
end loop

(using dbms_utility.execute_ddl_statement() ... )

for each row in pl/sql table

    disable constraint
end loop

for each row in pl/sql table

    truncate table
end loop

for each row in pl/sql table

    disable constraint
end loop

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

jawa wrote in message <7r95fp$4p6$1_at_schbbs.mot.com>...
>Here's how I do it. If anyone has a better way, post it:
>
>export ORACLE_HOME=your_oracle_home
>export ORACLE_SID=your_oracle_sid
>fileref=some_file
>
>$ORACLE_HOME/bin/sqlplus -s internal/ >${fileref} <<EOF
>set pages 0
>set lines 100
>set head off
>set echo off
>set echo off
>set verify off
>set feedback off
>select constraint_name from dba_constraints where table_name = 'table_name'
>and owner = 'owner';
>EOF
>
>cat ${fileref} |while read constr
>do
>$ORACLE_HOME/bin/sqlplus -s internal/ <<EOF
>alter table table_name disable constraint ${constr};
>EOF
>done
>
>$ORACLE_HOME/bin/sqlplus -s internal/ <<EOF
>truncate table table_name;
>EOF
>
>cat ${fileref} |while read constr
>do
>$ORACLE_HOME/bin/sqlplus -s internal/ <<EOF
>alter table table_name enable constraint ${constr};
>EOF
>done
>
>Regards,
>Jim
>
>
>Alex wrote in message <7r8rnh$nvc$1_at_nnrp1.deja.com>...
>>What's the best way to delete all data from a user's tables, while
>>keeping the table structure? Simply running DELETE FROM <table_name> for
>>all tables will not work because of a myriad of integrity constraints
>>without the ON DELETE CASCADE.
>>
>>Dropping all children before the parents doesn't seem appealing, because
>>the solution isn't general. Also, how would this handle the case where a
>>column that is a foreign key references a column in the same table?
>>
>>One solution I can think of is to write a PL/SQL procedure that gets all
>>integrity constraints for the user, drops them, gets all tables, deletes
>>data from them, and creates the integrity constraints again.
>>
>>Are there more elegant solutions? Any idea would be appreciated.
>>
>>
>>Alex
>>
>>
>>Sent via Deja.com http://www.deja.com/
>>Share what you know. Learn what you don't.
>
>
>
Received on Fri Sep 10 1999 - 02:16:36 CDT