RE: dropping a user's objects

Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g78JDNX08356 for ; Thu, 8 Aug 2002 15:13:23 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id MAA40713; Thu, 8 Aug 2002 12:10:05 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 004AFA35; Thu, 08 Aug 2002 11:56:46 -0800 Message-ID: Date: Thu, 08 Aug 2002 11:56:46 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Magaliff, Bill" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Magaliff, Bill" Subject: RE: dropping a user's objects Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C23F0C.E1D18F80" ------_=_NextPart_001_01C23F0C.E1D18F80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks - this looks good . . . I guess the best way is to specifically = drop each object type first, with tables last. =20 bill -----Original Message----- Sent: Thursday, August 08, 2002 2:56 PM To: Multiple recipients of list ORACLE-L This routine works for me, just replace with your user name (or prompt for it). Needs to be executed with user having dba privs... =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =20 store set sqlplus_settings replace=20 set heading off echo off termout off verify off feedback off pause off=20 set linesize 1000=20 set pagesize 0=20 set trimspool on=20 set long 1000000=20 spool dropall_temp.sql=20 -- drop private synonyms=20 select 'drop synonym '||owner||'.'||synonym_name||';'=20 from dba_synonyms=20 where owner =3D ''=20 order by owner, synonym_name=20 /=20 -- drop public synonyms=20 select 'drop public synonym '||synonym_name||';'=20 from dba_objects o, dba_synonyms s=20 where s.table_owner =3D ''=20 and o.owner =3D s.table_owner=20 and o.object_name =3D s.table_name=20 and o.object_type not in ('SYNONYM','PACKAGE BODY')=20 order by s.synonym_name=20 /=20 -- drop indexes=20 select 'drop index '||owner||'.'||index_name||';'=20 from dba_indexes=20 where owner =3D ''=20 and not exists=20 (select 'x'=20 from dba_constraints=20 where constraint_type in ('P','U')=20 and constraint_name =3D index_name)=20 order by owner, index_name=20 /=20 -- drop constraints=20 select 'alter table '||owner||'.'||table_name||' drop constraint '||=20 constraint_name||' cascade;'=20 from dba_constraints=20 where owner =3D ''=20 and constraint_type <> 'R'=20 order by owner, table_name, constraint_name=20 /=20 -- drop views=20 select 'drop view '||owner||'.'||view_name||';'=20 from dba_views=20 where owner =3D ''=20 order by owner, view_name=20 /=20 -- drop sequences=20 select 'drop sequence '||sequence_owner||'.'||sequence_name||';'=20 from dba_sequences=20 where sequence_owner =3D ''=20 order by sequence_owner, sequence_name=20 /=20 -- drop functions, packages, and procedures=20 select 'drop '||object_type||' '||owner||'.'||object_name||';'=20 from dba_objects=20 where owner =3D ''=20 and object_type in ('FUNCTION','PACKAGE','PROCEDURE')=20 and object_name <> 'DBS_VERIFY_USER'=20 order by owner, object_type, object_name=20 /=20 -- drop tables=20 select 'drop table '||owner||'.'||table_name||' cascade constraints;'=20 from dba_tables=20 where owner =3D ''=20 order by owner, table_name=20 /=20 spool off=20 @sqlplus_settings=20 @dropall_temp.sql=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =20 Dan Straub=20 McKesson Information Solutions=20 541-681-8278=20 44=B003'N 123=B005'W (or thereabouts)=20 Confidentiality Notice: This e-mail message, including any attachments, = is for the sole use of the intended recipient(s) and may contain = confidential and privileged information. Any unauthorized review, use, disclosure = or distribution is prohibited. If you are not the intended recipient, = please contact the sender by reply e-mail and destroy all copies of the = original message. ------_=_NextPart_001_01C23F0C.E1D18F80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by newsfeed.cts.com id MAA40713 RE: dropping a user's objects

Thanks=20 - this looks good . . . I guess the best way is to specifically drop each= object=20 type first, with tables last.

bill

-----Original Message-----
From: Straub, Dan=20 [mailto:Dan.Straub@McKesson.com]
Sent: Thursday, August 08, 2= 002=20 2:56 PM
To: Multiple recipients of list ORACLE-L
Subjec= t:=20 RE: dropping a user's objects

This routine works for me, just replace <user> = with your=20 user name (or prompt for it). Needs to be executed with user having dba= =20 privs...

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=20
store set sqlplus_settings replace

set heading off echo off termout off verify off feedb= ack off=20 pause off
set linesize 1000
set pagesize 0
set trimspool on=20
set long 1000000

spool dropall_temp.sql

-- drop private synonyms
se= lect 'drop=20 synonym '||owner||'.'||synonym_name||';'
from= =20 dba_synonyms
where owner =3D '<user>'=20
order by owner, synonym_name
/=20

-- drop public synonyms
sel= ect 'drop=20 public synonym '||synonym_name||';'
from dba_= objects=20 o, dba_synonyms s
where s.table_owner =3D=20 '<user>'
and o.owner =3D=20 s.table_owner
and o.object_name =3D=20 s.table_name
and o.object_type not in=20 ('SYNONYM','PACKAGE BODY')
order by=20 s.synonym_name
/

-- drop indexes
select 'dro= p index=20 '||owner||'.'||index_name||';'
from dba_index= es=20
where owner =3D '<user>'
=20 and not exists
        (select 'x' =
         from=20 dba_constraints
         where constra= int_type=20 in ('P','U')
           a= nd=20 constraint_name =3D index_name)
order by owne= r,=20 index_name
/

-- drop constraints
select = 'alter=20 table '||owner||'.'||table_name||' drop constraint '||
       constraint_name||'=20 cascade;'
from dba_constraints
where owner =3D '<user>'
= ; and=20 constraint_type <> 'R'
order by owner,=20 table_name, constraint_name
/

-- drop views
select 'drop = view=20 '||owner||'.'||view_name||';'
from dba_views<= /FONT>=20
where owner =3D '<user>'
order by=20 owner, view_name
/

-- drop sequences
select 'd= rop=20 sequence '||sequence_owner||'.'||sequence_name||';'
from dba_sequences
where sequence_ow= ner =3D=20 '<user>'
order by sequence_owner,=20 sequence_name
/

-- drop functions, packages, and procedures select 'drop '||object_type||' '||owner||'.'||object_name||';'= =20
from dba_objects
where own= er =3D=20 '<user>'
and object_type in=20 ('FUNCTION','PACKAGE','PROCEDURE')
and= =20 object_name <> 'DBS_VERIFY_USER'
order = by owner,=20 object_type, object_name
/

-- drop tables
select 'drop= table=20 '||owner||'.'||table_name||' cascade constraints;'
from dba_tables
where owner =3D=20 '<user>'
order by owner, table_name=20
/

spool off
@sqlplus_settings= =20
@dropall_temp.sql
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D

Dan Straub
McKesson Informa= tion=20 Solutions
541-681-8278
44=B003'N=20 123=B005'W (or thereabouts)
Confidentiality N= otice: This=20 e-mail message, including any attachments, is for the sole use of the i= ntended=20 recipient(s) and may contain confidential and privileged information.&n= bsp;=20 Any unauthorized review, use, disclosure or distribution is prohibited.= =20 If you are not the intended recipient, please contact the sender by rep= ly=20 e-mail and destroy all copies of the original=20 message.

------_=_NextPart_001_01C23F0C.E1D18F80-- -- 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).