Oracle database schema data removal

In my previous post I mentioned a script I used to drop everything from the schema, and I thought it could come handy to someone, hence here it is:

	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;

view raw gistfile1.sql hosted with ❤ by GitHub

This script has been extensively tested on Oracle 10g for all kind of editions and seems to work awesomely!
Beware, this WILL REMOVE EVERYTHING FROM THE DATABASE SCHEMA FOR USER YOU HAVE CONNECTED AS; that is if you connect as sysdba, it will most likely make the database useless.