Message-ID: <F001.004AFD5D.20020808134344@fatcity.com>
Date: Thu, 08 Aug 2002 13:43:44 -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

Bill,

Wouldn't DROP TABLE CASCADE CONSTRAINTS be easier,
or do you have some particular reason for doing it this way?

Jared


"Magaliff, Bill" <Bill.Magaliff@lendware.com>
Sent by: root@fatcity.com
08/08/2002 11:08 AM
Please respond to ORACLE-L

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


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


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