Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ....A drop schema script ??

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

From: Luis DeUrioste <luis_deurioste_at_rac.ray.com>
Date: Fri, 18 May 2001 08:38:37 -0700
Message-ID: <F001.00307873.20010518070552@fatcity.com>


Thank you Jared for the words of encouragement and specially for the "SCRIPT"

Luis Octavio

Jared Still wrote:

> 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).
Received on Fri May 18 2001 - 10:38:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US