Oracle: Drop all userobjects

Especially developers often have the need to clean out their dev-schema and get a fresh start with a given dump-set. A DBA usually would simply drop and recreate the schema. But for not to cross their DBA, kind developers are longing for a solution to carry out that cleaning autonomously. This could be done (e.g. in SQL-Plus) with a script like this. Please mind to execute this script in the “right” schema, as the dropping starts immediately without further inquiry!

-- This script drops all objects of the executing schema.
-- 2010-06-15 Marc Tempel (https://logbuffer.wordpress.com)

spool dropped_userobjects.log

set serveroutput on size 1000000
set heading off
set echo off
select 'Number of Userobjects before DROP: ' || count(*) from user_objects;
set heading on
set echo on

purge recyclebin;

begin

dbms_output.put_line ('###');
dbms_output.put_line ('Dropping all TRIGGERS:');
dbms_output.put_line ('###');

  for a in (

    select 'DROP ' || object_type || ' ' || object_name as kommando  
    from user_objects where object_type='TRIGGER'

  ) loop
    begin
      dbms_output.put_line (a.kommando);
EXECUTE IMMEDIATE a.kommando;
    exception
      when others then
        dbms_output.put_line (SQLERRM||'#');
    end;
  end loop;

 
dbms_output.put_line ('###');
dbms_output.put_line ('Dropping all CONSTRAINTS:');
dbms_output.put_line ('###');

  for a in (

    select 'ALTER TABLE ' || table_name || ' DROP CONSTRAINT ' || constraint_name as kommando 
    from (select table_name, constraint_name from user_constraints order by constraint_type desc)

  ) loop
    begin
      dbms_output.put_line (a.kommando);
EXECUTE IMMEDIATE a.kommando;
    exception
      when others then
        dbms_output.put_line (SQLERRM||'#');
    end;
  end loop;

 
dbms_output.put_line ('###');
dbms_output.put_line ('Dropping all TABLES:');
dbms_output.put_line ('###');

  for a in (

    select 'DROP ' || object_type || ' ' || object_name as kommando  
    from user_objects where object_type='TABLE'

  ) loop
    begin
      dbms_output.put_line (a.kommando);
EXECUTE IMMEDIATE a.kommando;
    exception
      when others then
        dbms_output.put_line (SQLERRM||'#');
    end;
  end loop;



EXECUTE IMMEDIATE 'purge recyclebin';


dbms_output.put_line ('###');
dbms_output.put_line ('Dropping all remaining objects:');
dbms_output.put_line ('### ');

  for a in (

    select 'DROP ' || object_type || ' ' || object_name as kommando  
    from user_objects

  ) loop
    begin
      dbms_output.put_line (a.kommando);
EXECUTE IMMEDIATE a.kommando;
    exception
      when others then
        dbms_output.put_line (SQLERRM||'#');
    end;
  end loop;
  
commit;
end;
/
purge recyclebin;
set heading off
set echo off
select 'Number of Userobjects after DROP: ' || count(*) from user_objects;
set heading on
set echo on
spool off
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: