Re: dropping a user's objects

Read consistency -- your same cursor is selecting both. The dictionary updates occur through a different mechanism so your open cursor still sees the old data. If you want to avoid it, drop the tables in a separate step, then query for indexes.

"Magaliff, Bill" 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_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: Don Jerman
  INET: djerman_at_dot.state.nc.us

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



text/x-vcard attachment: Card for Don Jerman