Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How can you wipe a set of Oracle schemas?

By wipe I mean remove all data, tables, views, procedures, synonyms, etc, etc.

share|improve this question

Start sqlplus as SYSTEM and then type:

drop user MYSCHEMA cascade;

Repeat for all the schemas that you want to drop.

share|improve this answer
Thanks - though I don't want to drop the actual user - just their "objects" – Marcus Leon Aug 27 '10 at 18:48
In this case it's a bit more involved, because you will need to disable all the referential integrity check, get the names of all the objects from the data dictionary and drop them one by one. It's much easier to let Oracle do the work: drop the user, then recreate it immediately afterwards, which will have the same end results as the one you want. – sebastianopilla Aug 27 '10 at 18:51
No - you can drop a table with CASCADE CONSTRAINTS and it'll automatically remove RI constraints. – Adam Musch Sep 2 '10 at 14:40

This is a beginning, not a fully baked script - I'm pretty sure it'll fail if the user has, for example, any queues configured with AQ.

  l_sql varchar2(2000);
  for i in (select object_type, object_name 
              from dba_objects
             where owner = :user_to_be_wiped
               and object_type in ('TYPE', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 
                                   'TABLE', 'VIEW', 'SEQUENCE', 'DATABASE LINK')
    l_sql := 'drop ' || :user_to_be_wiped || ' ' || i.object_type || '.' i.object_name;
    if i.object_type = 'TABLE' then
      l_sql := l_sql || ' cascade constraints';
    end if;
    execute immediate l_sql;
  end loop;
share|improve this answer

I find that to be able to drop the objects, you have to disable the foreign key constrants with the following.

select status,
 'alter table '||table_name||' disable constraint "' 
||constraint_name||'";' from user_constraints
where r_constraint_name is not null
order by owner,table_name

It that does not work, I drop the foreign key constraints with the following.

select status,
 'alter table '||table_name||' drop constraint "' 
||constraint_name||'";' from user_constraints
where r_constraint_name is not null
order by owner,table_name
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.