Skip to content

Instantly share code, notes, and snippets.

@psrdotcom
Last active June 25, 2023 15:09
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save psrdotcom/b61f8b401d4992428ecd8fb8225436c4 to your computer and use it in GitHub Desktop.
Save psrdotcom/b61f8b401d4992428ecd8fb8225436c4 to your computer and use it in GitHub Desktop.
Oracle DB: Delete all user objects (Tables, Views, Procedures, Functions, Sequences, Types, Packages)
BEGIN
FOR record IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'PROCEDURE',
'FUNCTION',
'SEQUENCE',
'TYPE',
'PACKAGE'
))
LOOP
BEGIN
IF record.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| record.object_type
|| ' "'
|| record.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| record.object_type
|| ' "'
|| record.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'FAILED: DROP '
|| record.object_type
|| ' "'
|| record.object_name
|| '"'
);
END;
END LOOP;
END;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment