Re: deleting all database objects belonging to a user

Roland Schaetzle (rsz_at_aifbodette.aifb.uni-karlsruhe.de) wrote:
: how can I delete (drop) all database objects belongig to a
: certain user in a simple way, i.e. I don't want to issue
: a 'drop xxx' command for ervery object (there are hundreds
: of those objects).
Hmm, merely dropping and recreating the user should do the trick, unless you also want to keep grants on the user as well without having to recreate them.

Personally, I use the following script to drop objects in my schema:

--
-- This script generates a script to drop all tables, procedures, functions,
-- and packages for a specified schema.  The resulting script should be
-- run under the specified schema or unpredictable results will occur.
--

set head off
set pause off
set verify off
set feedback off
set echo off
set termout off
set pagesize 50
set numformat 000000000000000000

spool delobjs.sql;

select 'drop ' || object_type || ' ' ||
       '&&1' || '.' || substr (object_name, 1,
                                    length (rtrim (object_name))) || ';'
  from sys.dba_objects
 where owner = upper ('&1')
 order by object_type desc;
   
spool off;

exit


I realize that this script generates a drop command for each object in
the schema, but I prefer the ability to edit the script and retain
certain objects within the schema in question.  On my project, we've
found that a lot of objects tend to get created during formal unit
testing and we need a way to clean up these objects without removing
the necessary objects.

I hope this helps out some.

Jonathan