SQL Script to empty out/cleanse a user schema
OK, it is a fairly trivial task. But when you have to prepare for workshops etc. it may take a lot of time to purge/cleanse/empty all student’s schemas without completely deleting them. Of course deleting them is an option but: it requires DBA privileges and it also removes all privileges granted to the users. What I want to do is simply drop every single object in the user’s schema, without losing the schema altogether. There may be excellent (GUI) tools for this, but my colleague Anton wrote this SQL script (in 1 minute and 44 seconds):
set pages 0 set feedback off spool del_schema_upx.sql select 'drop ' || object_type || ' ' || user || '.' || object_name || ';' from user_objects where object_type not in ( 'INDEX', 'TRIGGER', 'PACKAGE BODY') / spool off @del_schema_upx.sql
It does not yet cater for the fact that in 10g dropped objects are not gone but are in the recycle bin instead. They still show up in user_objects – with an unrecognizable name that starts with BIN. To get rid of them for good, you have to purge the recycle bin. The statement shown above will generate statements like: drop TABLE UP5.BIN$WE/4n30QTqaR8/1j4+ZDxw==$0; that will fail because tables in the bin can not be dropped. It also does not cater for the references (foreign keys) that may exist between tables. You may either have to run the script several times or add a statement that will first remove all Foreign Keys. The same applies to dropping of types on which (object) tables or columns in tables (VARRAY, NESTED TABLE) still depend. Finally it tried to drop tables that are created to implement a Materialized View whereas the Materialized View takes the table along with it when dropped.
So with a few adaptions, this is a very useful script – it left me time to write this post.