How to clean an oracle schema without administrative or dba permissions
Working in an environment where you do not have full administrative control over your oracle instances, may leave you wanting to clean your schema. The easiest way to do this is to drop and create the schema. When that is not an option, you will need to delete all the user objects yourself. After having to do this over and over again, I’ve put together a script that will allow you to clean your schema without having full permissions.
declare l_owner varchar2(30):='SCHEMA_NAME'; begin for x in (select object_name,object_type from dba_objects where owner=l_owner) loop if x.object_type='TABLE' then execute immediate 'drop table ' || l_owner|| '.' || x.object_name || ' cascade constraints purge'; elsif x.object_type not in ('INDEX','PACKAGE BODY','TRIGGER','LOB', 'TABLE PARTITION', 'TABLE SUBPARTITION', 'INDEX PARTITION', 'INDEX SUBPARTITION') then execute immediate 'drop ' || x.object_type || ' ' || l_owner || '.' || x.object_name; end if; end loop; end; /