03/08/2015

[Oracle] Purge schema

Purging a schema in Oracle isn't a straightforward procedure. Usually it's better to DROP the schema or the USER and recreate it.

But if you do not have the permissions to do that, or have other restrictions preventing you to perform the operation, you might find this piece of SQL code useful:

SELECT 'drop '||object_type||' '||object_name||' '||DECODE(object_type,'TABLE', ' cascade constraints;', ';') FROM USER_OBJECTS

This will generate drop statements for ALL objects in the schema it's run on. Just execute it after connecting as the user whose schema you want to purge, then copy the output and run it as script.

No comments:

Post a Comment

With great power comes great responsibility