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
add comment

2 Answers

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
add comment

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
add comment

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.