Drop all database objects in Oracle

In this article we will see a SQL script that will drop all objects in an Oracle database

There are cases where you need to test a SQL script that creates a database, and then it is almost certain that the need to clear the database from all objects is required. The SQL commands below, when executed they will create the DROP statements for the objects that currently exist.

To ensure proper execution, we firstly create the DROP statements for the triggers, then the sequences and finally for the tables. Specifically for the tables, the CASCADE CONSTRAINTS option is added to drop any constraints such as indexes, constrains, foreign keys, etc.

SELECT 'DROP '||object_type||' "'|| object_name || '";' 
FROM user_objects WHERE object_type IN ('TRIGGER');
 
SELECT 'DROP '||object_type||' "'|| object_name || '";' 
FROM user_objects WHERE object_type IN ('SEQUENCE');
 
SELECT 'DROP '||object_type||' "'|| object_name || '" CASCADE CONSTRAINTS;' 
FROM user_objects WHERE object_type IN ('TABLE');

This script was based on a StackOverflow answer found here.