RE: ....A drop schema script ??

Another thing with DROP USER CASCADE is you can cause lost data in the SYSTEM tablespace. This will cause corruption of data and inherently causes problems with export/import if I remember correctly.

I would recommend dropping all objects first, then drop the user. (There is one under objects on my site)
This was partially fixed in 8.1.6 and further refined on 8.1.7

Note:100405.1

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).
 <<Card for Luis Octavio de Urioste>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: cspence_at_FuelSpot.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).