Skip to content

Instantly share code, notes, and snippets.

@mmattozzi

mmattozzi/delete_all.sql

Created April 14, 2015 00:09
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save mmattozzi/933b630db3b8e09656ef to your computer and use it in GitHub Desktop.
Save mmattozzi/933b630db3b8e09656ef to your computer and use it in GitHub Desktop.
Download ZIP
Delete all objects in an Oracle Schema
Raw
delete_all.sql
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM all_objects
WHERE object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE') AND
owner = '<schema_name>')
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE' THEN
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
END;
END LOOP;
END;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment