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_at_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_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).
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_at_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_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).
Received on Thu Aug 08 2002 - 17:28:33 CDT
