Re: Deleting all data from tables

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 09 Sep 1999 20:06:24 +0800
Message-ID: <37D7A2C0.352@yahoo.com>

Alex wrote:
>
> 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.

something along of the lines of:

spool disable.sql
select 'alter table '||table_name||' disable constraint '||constraint_name||';'
from user_constraints
where constraint_type = 'R';
spool off
@disable
spool truncate.sql
select 'truncate table '||table_name||';' from user_tables;
spool off
@truncate
spool enable.sql
select 'alter table '||table_name||' enable constraint '||constraint_name||';'
from user_constraints
where constraint_type = 'R';
spool off
@enable

should do the trick
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Thu Sep 09 1999 - 07:06:24 CDT