schema clean up

[jack999]

I want to find out what objects have been created in a schema and drop all the objects. How to do that, assuming that I cannot delete the user?

[davey23uk]

select object_name, object_type from user_objects

then something like

spool drop.sql

select 'drop ' ||object_type|| ' '||object_name|| ';' from user_object where object_type in ('VIEW','TABLE','TRIGGER','PACKAGE','PACKAGE BODY','SEQUENCE','PROCEDURE');

My syntax is probably wrong and I have probbaly missed out object type, but you get the idea

[sysdba]

write your owner script. Something like:

spool drop_all_object.sql

select 'drop ' || object_type
|| ' &&owner..' ||object_name
|| decode(object_type, 'TABLE', ' CASCADE constraint') ||';'
from dba_objects where owner = '&&owner'
/

spool off
@drop_all_object.sql

[davey23uk]

chances are if he cannot drop a user, he wont have access to the dba_* views