I'll add that if you have foreign keys, they will mess you up. Here is an
old script I have in my stash that you can modify with info already provided
by others.
procedure truncate_all(code_word in varchar2 default 'XXX') is
cursor c1 is select table_name,constraint_name from user_constraints
where constraint_type = 'R';
cursor c2 is select table_name from user_tables;
handle integer;
rows integer;
begin
if code_word != 'NUKE' then
raise_application_error(-20001, 'WRONG MAGIC WORD, BUCKWHEAT.');
end if;
dbms_output.enable(100000);
handle := dbms_sql.open_cursor;
for t in c1 loop
dbms_sql.parse(handle,'alter table '||t.table_name||' disable
constraint '||t.constraint_name, dbms_sql.native);
rows := dbms_sql.execute(handle);
end loop;
for t in c2 loop
dbms_sql.parse(handle,'truncate table '||t.table_name,
dbms_sql.native);
rows := dbms_sql.execute(handle);
end loop;
for t in c1 loop
dbms_sql.parse(handle,'alter table '||t.table_name||' enable
constraint '||t.constraint_name, dbms_sql.native);
rows := dbms_sql.execute(handle);
end loop;
exception
when others then
dbms_output.put_line(SQLCODE||': '||SQLERRM);
end;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
