Oracleでユーザのすべてのオブジェクトを削除するPL/SQLコード

Oracleでユーザのすべてのオブジェクトを削除するPL/SQLコードを書いたのでメモ。

使い方

sqlplus -S usr/password@tnsname < drop_all_user_objects.sql

drop_all_user_objects.sql

-- set serveroutput on

declare
  procedure drop_all_user_objects_of_type(p_object_type USER_OBJECTS.OBJECT_TYPE%type) as
    v_object_type USER_OBJECTS.OBJECT_TYPE%type := upper(p_object_type);
    cursor c1 is
      select OBJECT_NAME from USER_OBJECTS
      where OBJECT_TYPE = v_object_type;
    v_row c1%rowtype;
    v_sql varchar2(100);
  begin
    open c1;
    loop
      fetch c1 into v_row;
      exit when c1%notfound;

      -- DROP FUNCTION name;
      -- DROP PROCEDURE name;
      -- DROP PACKAGE name;
      -- DROP MATERIALIZED VIEW name;
      -- DROP TABLE name CASCADE CONSTRAINTS PURGE;
      -- DROP SYNONYM name FORCE;
      -- DROP SEQUENCE name;
      -- DROP TYPE name FORCE;
      v_sql := 'DROP ' || v_object_type || ' ' || v_row.object_name;
      case v_object_type
      when 'TABLE'   then v_sql := v_sql || ' CASCADE CONSTRAINTS PURGE';
      when 'SYNONYM' then v_sql := v_sql || ' FORCE';
      when 'TYPE'    then v_sql := v_sql || ' FORCE';
      else null;
      end case;
      -- dbms_output.put_line(v_sql);
      execute immediate v_sql;
    end loop;
    close c1;
  end drop_all_user_objects_of_type;
begin
  drop_all_user_objects_of_type('FUNCTION');
  drop_all_user_objects_of_type('PROCEDURE');
  drop_all_user_objects_of_type('PACKAGE');
  drop_all_user_objects_of_type('SYNONYM');
  drop_all_user_objects_of_type('SEQUENCE');
  drop_all_user_objects_of_type('TYPE');
  drop_all_user_objects_of_type('MATERIALIZED VIEW');
  drop_all_user_objects_of_type('TABLE');
end;
/