Greg,
Try
this:
create
or replace procedure truncate_tables(in_owner in varchar2)
is
BEGIN
FOR r IN (SELECT table_name FROM dba_tables WHERE
owner=in_owner)
LOOP
EXECUTE IMMEDIATE 'truncate table ' || in_owner || '.' || r.table_name;
END LOOP;
END;
/
You
will need to grant select on DBA_TABLES to the owner of this procedure.
You may also need to grant ALTER ANY TABLE to the same user to allow you to
perform the Truncate function. I forget exactly which priv you need to do
this - but something extra needs to be granted to the owner of the procedure to
perform the trancate function.
Good
Luck
Tom
Mercadante
Oracle Certified
Professional
Hi!
I need to wrap this SQL into SP where I can pass new owner name.
I'm planning create db table with all owners I need to pass.
BEGIN
FOR r IN (SELECT table_name FROM dba_tables WHERE
owner='TRAIN1')
LOOP
EXECUTE IMMEDIATE
'truncate table TRAIN1.'||r.table_name;
END LOOP;
END;
/
Thanks.
Greg
Do you Yahoo!?
Yahoo! Hotjobs: Enter
the "Signing Bonus" Sweepstakes
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: thomas.mercadante_at_labor.state.ny.us
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: ListGuru_at_fatcity.com (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).
Received on Thu Jan 08 2004 - 14:34:35 CST
