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.
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;
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; /
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
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.
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.