RE: dropping a user's objects

Jared:

I had originally developed this so that I could eventually alter it to just drop certain parts of the schema (PK's, Unique constraints, etc.) based on input values, although I never did that.

I suppose I could drop tables first with the cascade constraints clause, and then drop all other objects.

-bill

-----Original Message-----
Sent: Thursday, August 08, 2002 4:35 PM
To: ORACLE-L_at_fatcity.com
Cc: Bill.Magaliff_at_lendware.com

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_at_lendware.com> Sent by: root_at_fatcity.com
08/08/2002 11:08 AM
Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_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_at_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_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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Magaliff, Bill
  INET: Bill.Magaliff_at_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_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).