Re: Deleting all data from tables

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.

begin 666 Jim Wadas.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..E=A9&%S.TII;0T*1DXZ2FEM M(%=A9&%S#0I/4D<Z36]T;W)O;&$@0T4[25134R!#;W)E(%!R;V-E<W-E<PT* M5$E43$4Z4')I;F-I<&QE($5N9VEN965R+"!3=&%F9_at_T*5$5,.U=/4DL[5D]) M0T4Z-#@P+30T,2TX,3DV#0I414P[4$%'15([5D])0T4Z-C R+3(R,RTP,S V M#0I414P[5T]22SM&05_at_Z-#@P+30T,2TV,3_at_Q#0I!1%([5T]22SM%3D-/1$E. M1SU154]4140M4%))3E1!0DQ%.CM3>7-T96US(%-O;'5T:6]N<R!'<F]U<#LX M,C(P($4N(%)O;W-E=F5L="!3="X],$0],$%-1#H_at_4C,R,S@[4V-O='1S9&%L M93M!6CL]#0HX-3(U-SM54T$-"DQ!0D5,.U=/4DL[14Y#3T1)3D<]455/5$5$ M+5!224Y404),13I3>7-T96US(%-O;'5T:6]N<R!'<F]U<#TP1#TP03_at_R,C @ M12X_at_4F]O<V5V96QT(%-T+CTP1#TP04U$.B!2,S(S.#TP1#TP05-C;W1T/0T* M<V1A;&4L($%:(#@U,C4W/3!$/3!!55-!#0I%34%)3#M04D5&.TE.5$523D54 M.DII;2Y7861A<T!M;W1O<F]L82YC;VT-"E)%5CHQ.3DY,#DP.50R,#,Q-3!: -#0I%3D0Z5D-!4D0-"@``
`
end Received on Thu Sep 09 1999 - 15:31:50 CDT