Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

3 Answers 3

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
1  
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.

declare
  l_sql varchar2(2000);
begin
  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')
  loop
    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;
end;
/
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

 
discard

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.