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;
/