Message-ID: <F001.004B01A3.20020808163325@fatcity.com>
Date: Thu, 08 Aug 2002 16:33:25 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Jared.Still@radisys.com
Subject: Re: dropping a user's objects
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

Jack,

That works well for small schemas.  It tends to generate a
lot of recursive SQL when the schema has more than a 
couple hundred objects.

This number varies with how intensely RI was implemented.

More RI, *lots* more recursive SQL.

When the schema has many objects, it's much faster to drop 
a table at a time with cascade constraints,

Think dropping a table with 100k extents.  :)

Jared


Jack Silvey <jack_silvey@YAHOO.COM>
Sent by: root@fatcity.com
08/08/2002 03:28 PM
Please respond to ORACLE-L

 
        To:     Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
        cc: 
        Subject:        Re: dropping a user's objects


Bill,

Pull out the big cannon and go boom:

export the CREATE USER to create_user script

write a script containing: 

 DROP USER uname CASCADE;
 @CREATE_USER

run this new script.

have beer.

Now of course, this will not be as fast as a parallel
DROP TABLE user.tab CASCADE CONSTRAINTS, but hey, more
time to have beer.

hth,

Jack


--- "Magaliff, Bill" <Bill.Magaliff@lendware.com>
wrote:
> Good day, all:
> 
> I have a PL/SQL routine that I use to drop all
> objects owned by a given
> user, which I'm attaching below. 
> 
> I first drop primary keys, then unique constraints,
> and finally all other
> objects.  The routine to drop all additional objects
> uses a loop to select
> the object name and then drops it.  As you can see
> by the output below
> (which is just for this last piece), I drop a table
> and then the next piece
> is to drop the index, which doesn't exist once the
> table is dropped
> (obviously).
> 
> However, why is the cursor even finding the index in
> the data dictionary
> once the table is dropped? Is the data dictionary
> not updated that quickly?
> 
> thanks
> 
> bill
> 
> 
> ********************************************
> 
> ROUTINE:
> 
> declare
> 
> v_cname varchar2(30);
> v_tname varchar2(30);
> v_oname varchar2(30);
> v_otype varchar2(30);
> 
> cursor get_pk is
> select table_name from user_constraints
> where constraint_type = 'P';
> 
> cursor get_unique is
> select constraint_name, table_name from
> user_constraints
> where constraint_type = 'U';
> 
> cursor get_object is
> select object_name, object_type from user_objects;
> 
> begin
> 
> open get_pk;
> loop
> fetch get_pk into v_tname;
> exit when get_pk%notfound;
> dbms_output.put_line ('dropping primary key on ' ||
> v_tname);
> execute immediate ('alter table ' || v_tname || '
> drop primary key
> cascade');
> end loop;
> close get_pk;
> 
> open get_unique;
> loop
> fetch get_unique into v_cname, v_tname;
> exit when get_unique%notfound;
> dbms_output.put_line ('dropping unique constraint '
> || v_cname || ' on ' ||
> v_tname);
> execute immediate ('alter table ' || v_tname || '
> drop constraint ' ||
> v_cname || ' cascade');
> end loop;
> close get_unique;
> 
> open get_object;
> loop
> fetch get_object into v_oname, v_otype;
> exit when get_object%notfound;
> dbms_output.put_line ('dropping ' || v_otype || ' '
> || v_oname);
> execute immediate ('drop ' || v_otype || ' ' ||
> v_oname);
> end loop;
> close get_object;
> 
> end;
> /
> 
> 
>
**************************************************************
> 
> OUTPUT
> 
> SQL> @y:\scripts\drop_all_objects
> dropping SEQUENCE AMDC_DOCUMENT_SEQ
> dropping TABLE AMDC_DOC_HISTORY
> dropping INDEX AMDC_DOC_HISTORY_I1
> declare
> *
> ERROR at line 1:
> ORA-01418: specified index does not exist
> ORA-06512: at line 44
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Magaliff, Bill
>   INET: Bill.Magaliff@lendware.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@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).


__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: jack_silvey@yahoo.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@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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still@radisys.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@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).