Wipe data from Oracle DB

Question

I'm running some test to migrate data from one database to another and for that i need to delete and recreate same tables, views and other stuff. So what is the SQL statement(s) in Oracle to wipe everything (delete Tables, Views, Sequences, Functions, Procedures, etc.). I know that I can use "DROP" but sometimes that's not convenient enough.

Thanks

Answer 1

I've been in that scenario before, what I do is restore a backup and overwrite the current schema.

That way I have everything back to square one.

Answer 2

I've found this script where you can generate script to drop everything, but it would be awesome if I use something generic.

set feedback off
set pagesize 0
spool AllObjectsDrop.sql
select 'drop view '||view_name||';' from user_views;
select distinct 'drop sequence '||sequence_name|| ';'from user_sequences;
select distinct 'drop table '||table_name|| ';'from user_tables;
select distinct 'drop procedure '||name|| ';'from user_source where type = 'procedure';
select distinct 'drop function '||name|| ';'from user_source where type = 'function';
select distinct 'drop package '||name|| ';'from user_source where type = 'package';
select 'drop synonym '||synonym_name||';' from user_synonyms where synonym_name not like 'sta%' and synonym_name like 's_%'
spool off

Answer 3

If you have Enterprise Edition you should look into restore points and Flashback Database.

DROP USER ... CASCADE is good if you have privileges and a quick script to recreate the user.

Answer 4

The easiest way would be to drop the schema the objects are associated to:

DROP USER [schema name] CASCADE

Nuke it from orbit - it's the only way to be sure ;)

For the script you provided, you could instead run those queries without having to generate the intermediate script using the following anonymous procedure:

BEGIN

  --Bye Views!
  FOR i IN (SELECT uv.view_name
              FROM USER_VIEWS uv) LOOP
    EXECUTE IMMEDIATE 'drop view '|| i.view_name ||'';
  END LOOP;

  --Bye Sequences!
  FOR i IN (SELECT us.sequence_name
              FROM USER_SEQUENCES us) LOOP
    EXECUTE IMMEDIATE 'drop sequence '|| i.sequence_name ||'';
  END LOOP;

  --Bye Tables!
  FOR i IN (SELECT ut.table_name
              FROM USER_TABLES ut) LOOP
    EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS ';
  END LOOP;

  --Bye Procedures/Functions/Packages!
  FOR i IN (SELECT us.name,
                   us.type
              FROM USER_SOURCE us
             WHERE us.type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
          GROUP BY us.name, us.type) LOOP
    EXECUTE IMMEDIATE 'drop '|| i.type ||' '|| i.name ||'';
  END LOOP;

  --Bye Synonyms!
  FOR i IN (SELECT ut.synonym_name
              FROM USER_SYNONYMS us
             WHERE us.synonym_name NOT LIKE 'sta%' 
               AND us.synonym_name LIKE 's_%') LOOP
    EXECUTE IMMEDIATE 'drop synonym '|| i.synonym_name ||'';
  END LOOP;

END;

Answer 5

In the cases where I can't simply drop the schema, I use the following script:

declare
    cursor c_get_objects is
     select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name from user_objects
     where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','PROCEDURE','FUNCTION','SYNONYM', 'MATERIALIZED VIEW')
     order by object_type;
    cursor c_get_objects_type is 
     select object_type, '"'||object_name||'"' obj_name from user_objects where object_type in ('TYPE');
begin
    for object_rec in c_get_objects loop
     execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
    end loop;
    for object_rec in c_get_objects_type loop
     execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name || ' force');
    end loop;
end;
/