Re: Drop a user's objects

From: Bern Anderson <bern_anderson_at_RL.GOV>
Date: Mon, 15 Jan 1996 09:28:16 PST
Message-Id: <9601151735.AA17818@alice.jcc.com>

Kjetil,

I use a dynamic SQL script to accomplishs thing like this; see below.

---

rem * DROP_OBJ.SQL
rem * BJA 11/04/94 for Oracle Ver 7.
rem * Dynamic SQL to drop user objects for a user ID.
rem * MUST have DBA privs to execute this file!
rem * Execute CONST_DROP.SQL first to remove constraints.



/*      Modifications -

11/04/94 BJA Drop objects by executing the spooled file. */

prompt
prompt Enter the Owner's name.
prompt
accept owner prompt 'Owners Name: >'
prompt

prompt
prompt Enter the Object type.
prompt
accept object_type prompt 'Object Type: >' prompt

prompt
prompt Enter the Object Name.
prompt
accept object_name prompt 'Object Name: >' prompt

set term off
set head off
set echo off
set feedback off
set verify off
set pause off
set pagesize 1000

clear columns
clear breaks
clear computes
clear buffer

SPOOL do.sql

select

        'drop '||object_type||' '||owner||'.'||object_name||';' from

        dba_objects
where

        owner like upper('&owner') and
        object_type like upper('&object_type') and
        object_name like upper('&object_name')
order by
        object_type,
        object_name

/

SPOOL off

set head on
set feedback off
set echo on
set verify off
set pause on
set pause 'More -> RETURN'
set pagesize 22

clear columns
clear breaks
clear computes
clear buffer

undefine owner
undefine object_name
undefine object_type

set term on

prompt Review and execute 'do.sql' to enable constraints!

rem * exit

---

You may want to create a simular one to drop, enable, disable constraints if you have constraints enabled. If you don't have DBA privs, change DBA_OBJECTS to ALL_OBJECTS. If script length is a factor, you can delete what is not necessary to support your environment; I usually build my SQL scripts in a modular fashion, so I can cut and paste to save time when I have to create another one.

Regards,

Bern

---

Bern Anderson                           Phone: 509-373-3186
BOEING IS                               FAX:   509-372-1336
P.O. Box 300  M/S: H8-42
Richland, WA  99352-0300                Email: bern_anderson_at_FEP1.RL.GOV
------------------------------------------------------------------------

Received on Mon Jan 15 1996 - 12:35:31 CST