Script to drop all objects in a schema

drop_all_objects_of_selected_user.sql

 REM name: drop_objects_of_selected_user.sql
  REM usage:@drop_objects_of_selected_user.sql
  REM -------------------------------------------------------------------------
  REM requirements: dba-role
  REM -------------------------------------------------------------------------
  REM -------------------------------------------------------------------------
  REM Purpose:
  REM This script drops all objects (except DATABASE LINK) of a user, which must
  REM be typed in after this script was started. Only one user can be specified
  REM for each run. The user typed in must be different to SYS or SYSTEM.
  REM -------------------------------------------------------------------------
  REM -------------------------------------------------------------------------
select 'Attention ! This script will drop all objects of the user you type in !' ATTENTION from dual;
select 'If you are not sure to finish, so break with ctrl+d !' ATTENTION from dual;
accept user_to_clean prompt "Enter user to drop all his objects: "
set linesize 120 heading off termout off pagesize 2000 recsep 0 trimspool on
set feedback off echo off show off ver off
set serveroutput on
spool drop_all_objects_of_selected_user_l1.sql
declare
        cnt_user_objects number;
        ora_version number;
begin
        dbms_output.enable;
        select max(to_number(substr(version,1,1))) into ora_version from dba_registry;
        IF ora_version > 4 THEN
--              dbms_output.put_line('This is Oracle-version lower than version 10');
                dbms_output.put_line('set feedback off echo off show off ver off');
                dbms_output.put_line('set linesize 120 heading off termout off pagesize 2000 trimspool on');
                dbms_output.put_line('spool drop_all_objects_of_selected_user_l2.sql');
                dbms_output.put_line('select '||'''spool drop_all_objects_of_user_'''||'||'||'''&&user_to_clean'''||'||'||'''.log'''||' from dual;');
                dbms_output.put_line('select '||'''drop table "'''||'||'||'owner'||'||'||'''"."'''||'||'||'table_name'||'||'||'''" cascade constraints;'''||' from dba_tables where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||');');
                dbms_output.put_line('select '||'''drop '''||'||'||'object_type'||'||'||''' "'''||'||'||'owner'||'||'||'''"."'''||'||'||'object_name'||'||'||'''";'''||' from dba_objects where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||') AND object_type not in ('||'''TABLE'''||','||'''DATABASE LINK'''||','||'''INDEX'''||','||'''TRIGGER'''||','||'''PACKAGE BODY'''||','||'''LOB'''||');');
                dbms_output.put_line('select '||'''spool off'''||' from dual;');
                dbms_output.put_line('spool off');
                dbms_output.put_line('@@drop_all_objects_of_selected_user_l2.sql');
        ELSE
--              dbms_output.put_line('This is Oracle-version 10. Note: Objects are dropped with purge-option!');
                dbms_output.put_line('set feedback off echo off show off ver off');
                dbms_output.put_line('set linesize 120 heading off termout off pagesize 2000 trimspool on');
                dbms_output.put_line('spool drop_all_objects_of_selected_user_l2.sql');
                dbms_output.put_line('select '||'''spool drop_all_objects_of_user_'''||'||'||'''&&user_to_clean'''||'||'||'''.log'''||' from dual;');
                dbms_output.put_line('select '||'''drop table "'''||'||'||'owner'||'||'||'''"."'''||'||'||'table_name'||'||'||'''" cascade constraints purge;'''||' from dba_tables where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||');');
                dbms_output.put_line('select '||'''drop '''||'||'||'object_type'||'||'||''' "'''||'||'||'owner'||'||'||'''"."'''||'||'||'object_name'||'||'||'''";'''||' from dba_objects where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||') AND object_type not in ('||'''TABLE'''||','||'''DATABASE LINK'''||','||'''INDEX'''||','||'''TRIGGER'''||','||'''PACKAGE BODY'''||','||'''LOB'''||');');
                dbms_output.put_line('select '||'''spool off'''||' from dual;');
                dbms_output.put_line('spool off');
                dbms_output.put_line('@@drop_all_objects_of_selected_user_l2.sql');
        END IF;
        EXCEPTION
        WHEN OTHERS THEN
                dbms_output.put_line(SQLERRM);
END;
/
spool off
@@drop_all_objects_of_selected_user_l1.sql
set termout on
set serveroutput on
declare
        cnt_user_objects number;
begin
dbms_output.enable;
select count(*) into cnt_user_objects from dba_objects where owner=upper('&&user_to_clean');
        IF cnt_user_objects > 0
           THEN
                dbms_output.put_line('Please restart this script again ! There are objects left in schema '||'&&user_to_clean'||' !');
           ELSE
                dbms_output.put_line('All objects of user '||'&&user_to_clean'||' were dropped !');
        END IF;
        EXCEPTION
        WHEN OTHERS THEN
                dbms_output.put_line(SQLERRM);
END;
/
!rm drop_all_objects_of_selected_user_l1.sql
!rm drop_all_objects_of_selected_user_l2.sql