Bill,
Wouldn't DROP TABLE CASCADE CONSTRAINTS be easier,
or do you have some particular reason for doing it this way?
Jared
"Magaliff, Bill" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/08/2002 11:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
