Use the following script to drop all objects in a specific schema in an Oracle Database without droping the schema itself. It saves you from recreating the schema, its associated user, its rights, etc.

create script file

Create a file called empty_user.sql with the following content:

purge recyclebin;
declare
    -- FK first, then unique, then PK
    cursor cursor_constraints is select table_name, constraint_name
                          from user_constraints
                         where constraint_type in ('P', 'R', 'U')
                        order by decode(constraint_type, 'R', 0, 'U', 1, 'P', 2, 3);
    cursor cursor_mviews is select mview_name from user_mviews;
    cursor cursor_views is select view_name from user_views;
    cursor cursor_mviews_logs is select master from user_mview_logs;
    cursor cursor_tables is select table_name from user_tables;
    cursor cursor_synonyms is select synonym_name from user_synonyms;
    cursor cursor_sequences is select sequence_name from user_sequences;
begin
    for current_val in cursor_constraints
    loop
        execute immediate 'alter table ' || current_val.table_name || ' drop constraint ' || current_val.constraint_name;
    end loop;
    for current_val in cursor_mviews
    loop
        execute immediate 'drop materialized view ' || current_val.mview_name;
    end loop;
    for current_val in cursor_views
    loop
        execute immediate 'drop view ' || current_val.view_name;
    end loop;
    for current_val in cursor_mviews_logs
    loop
        execute immediate 'drop materialized view log on ' || current_val.master;
    end loop;
    for current_val in cursor_tables
    loop
        execute immediate 'drop table ' || current_val.table_name || ' purge';
    end loop;
    for current_val in cursor_synonyms
    loop
        execute immediate 'drop synonym ' || current_val.synonym_name;
    end loop;
    for current_val in cursor_sequences
    loop
        execute immediate 'drop sequence ' || current_val.sequence_name;
    end loop;
end;
/
quit

line with “/” is required to execute the preceding PL/SQL procedure
line with “quit” is usefull to automatically exit sqlplus when procedure has been executed

Execute with sqlplus

sqlplus user/user_pwd @empty_user.sql

You can expect output such as the following:

my_machine:$ sqlplus user/user_pwd @empty_user.sql

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 14 12:27:32 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


Recyclebin purged.


PL/SQL procedure successfully completed.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Credits

The origin of the above script is one largely used at Ekino to which multiple developers (including myself) contributed.