schema clean up
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?
select object_name, object_type from user_objects
then something like
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
Last edited by davey23uk; 11-19-2003 at 05:59 PM.
write your owner script. Something like:
select 'drop ' || object_type
|| ' &&owner..' ||object_name
|| decode(object_type, 'TABLE', ' CASCADE constraint') ||';'
from dba_objects where owner = '&&owner'
chances are if he cannot drop a user, he wont have access to the dba_* views
Click Here to Expand Forum to Full Width