Skip to content

Instantly share code, notes, and snippets.

@esaounkine
Created April 29, 2011 07:53
Show Gist options
  • Save esaounkine/948006 to your computer and use it in GitHub Desktop.
Save esaounkine/948006 to your computer and use it in GitHub Desktop.
Oracle database schema data removal
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN
(SELECT object_name,
object_type
FROM user_objects
WHERE object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE', 'TRIGGER')
)
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;
/
PURGE RECYCLEBIN;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment