I remember seeing a script to recursively drop all user objects (for a specific schema), so if the user want to refresh their own schema, they can use this script to drop all objects they have first, and then using an dump file to refresh.
The script I was seeing uses a 'for cursor' loop to find all objects use owns and drop them on the fly (use 'execute immediate'). But I can't really it anymore, so I wonder anybody can help me on this?
set trimspool on
set pagesize 0
set line 1000
set feed off
set verify off
select 'prompt Conectando como &&OWNER' || chr(10) || 'connect &&OWNER' DROP_OBJECTS
select 'spool dropall.log' DROP_OBJECTS
when object_type = 'TABLE' then 'drop ' || object_type
|| ' ' || owner || '.' || object_name ||' cascade constraints'
when object_type = 'PACKAGE BODY' then 'prompt PACKAGES BODY'
when object_type = 'INDEX' then 'prompt INDEXES'
when object_type = 'DATABASE LINK' then 'drop ' || object_type
|| ' ' || object_name
else 'drop ' || object_type || ' ' || owner || '.' || object_name
end || ';')
where owner = '&&OWNER'
and object_type not like '%PARTITION%'
select 'drop public synonym '||synonym_name||';'
where table_owner = '&&OWNER'
and owner = 'PUBLIC'
select 'spool off'
Yes, dropping the user with the CASCADE option is quicker, but there are a number of reasons why I might not pick that option:
1) I might want to only drop certain objects. If you see my script, I limit the object types that I reference.
2) If there are only a few objects I might clean up with a drop, rather than trash the whole user.
3) Some of my users have loads of privileges that I don't want to recreate, especially all the administrative ones granted via packages like the AQ and Java. For these I may decide to do a drop of the objects, rather than the user.
If of course you make a habit of regularly clearing out the contents of users, I guess doing a full drop and recreate of the user is a better option. Horses for courses!
Alot of this is just preference, not science, but it's not something you do all the time, so I don't get precious about it