how can i drop all the objects in my schema?

Question

I have a Platform database setup in QA. I want to rebuild the schema from scratch, but I can't drop the user like I do in dev because I don't have permissions.

I know I can change "setup.schema=true" in node.properties, but this won't help if there are already objects in the schema. Can I drop all my tables/etc. without dropping the whole user?

MythriPericharla · Accepted Answer

Running following sql will generate sql statements which need to be run to delete all database objects.

select * from (select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects
where object_type not in ('INDEX', 'LOB', 'SYNONYM')
order by decode(object_type, 'SEQUENCE', 0, 'TRIGGER', 1, 'VIEW', 2, 'MATERIALIZED VIEW', 2.1, 'TABLE', 3, 'FUNCTION', 4, 'PACKAGE', 5, 'PACKAGE BODY', 6, 'PROCEDURE', 7, 'TYPE', 8, 9), object_type, object_name)
union all select 'purge recyclebin;' from dual;

Above sql's are available in p4 @ //install-staging-depot/main/schema/relational/oracle/maint/drop _all_objects.sql

If you want to delete only specific objects, you can modify the query to filter on object_type or other factors.

[Solved] how can i drop all the objects in my schema?