Luis,
Don't feel bad, this is a valid question.
DROP USER CASCADE is not always the most efficient way to
drop a user, especially a user with a large number of objects.
I've had that command fail a number of times, and had to rerun
it until I decided there must be a better way.
DROP USER CASCADE can generate a *lot* of recursive SQL,
and therefore can take a very long time. It also eats up system
resources at a prodigious rate.
I've found that when dropping a schema owner, it's much easier
to drop their objects individually, then drop the user.
e.g.
spool '_dropscott.sql'
select 'drop ' || object_type || ' scott.' || object_name ||
decode(object_type, 'TABLE', ' cascade constraints;',';')
from dba_objects
where object_type in ('TABLE','PACKAGE','PROCEDURE','FUNCTION')
and owner = 'SCOTT';
spool off
@_dropscott.sql
drop user scott cascade;
I still include the 'cascade' on drop user to pick up any objects not
included in the above list.
Works for me, YMMV.
Jared
On Thursday 17 May 2001 13:25, Luis DeUrioste wrote:
> uhhh DUHHH !
>
> I could write the algorithm to solve the logarithm ...... IF I ONLY HAD A
> BRAIN ......
>
> Thank you to all of you, and special thanks for not bashing me
>
> Luis Octavio
>
> Luis DeUrioste wrote:
> > All mighty and powerful DBA supremes !
> >
> > Do any of you have a drop schema script that would be willing to share
> > ??
> > It'll be greatly appreciated
> >
> > T I A
----------------------------------------
Content-Type: text/x-vcard; charset="us-ascii"; name="Luis_deUrioste.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Luis Octavio de Urioste
----------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
