Re: ....A drop schema script ??

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: jkstill_at_cybcon.com

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