Oracle Snippets Part 1 – How to clear a schema in Oracle

This script deletes all user objects in a schema (Tables, views, procedures, dblinks, etc…) without dropping the user.

SQL Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
conn test/test

set head off
set pagesize 0
set verify off
set feedback off

select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects

spool drop_objects.sql
/
spool off

@drop_objects.sql

original source: https://community.oracle.com/thread/328462