Re: Deleting all data from tables

Alex wrote at 09-Sep-99 18:45:26
on the subject Deleting all data from tables:

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

Hello Alex,

just an idea:
How about exporting all tables of the user with ROWS=N then drop user <username> cascade;
And finally reimport the empty tables.

>Alex

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Hauptstr. 26            | lothar.armbruester_at_t-online.de
D-65346 Eltville        |