OracleでINVALIDなオブジェクトを表示するSQL

使い方

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

show_invalid_objects.sql

column OBJECT_TYPE format A20
column OBJECT_NAME format A50

select OBJECT_TYPE, OBJECT_NAME from USER_OBJECTS
where STATUS = 'INVALID'
order by OBJECT_TYPE, OBJECT_NAME;

Oracleで全てのオブジェクトを再コンパイルするPL/SQLコード

Oracleで全てのオブジェクトを再コンパイルするPL/SQLコードを書いたのでメモ。

使い方

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

compile_all_user_objects.sql

set serveroutput on

declare
  procedure compile_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;

      -- ALTER FUNCTION name COMPILE;
      -- ALTER PROCEDURE name COMPILE;
      -- ALTER PACKAGE name COMPILE;
      -- ALTER SYNONYM name COMPILE;
      -- ALTER TRIGGER name COMPILE;
      -- ALTER TYPE name COMPILE;
      v_sql := 'ALTER ' || v_object_type || ' ' || v_row.object_name || ' COMPILE';
      -- dbms_output.put_line(v_sql);
      begin
        execute immediate v_sql;
      exception
        when others then
          dbms_output.put_line('Compilation error ' || v_row.object_name);
      end;
    end loop;
    close c1;
  end compile_user_objects_of_type;
begin
  compile_user_objects_of_type('TYPE');
  compile_user_objects_of_type('SYNONYM');
  compile_user_objects_of_type('TRIGGER');
  compile_user_objects_of_type('FUNCTION');
  compile_user_objects_of_type('PROCEDURE');
  compile_user_objects_of_type('PACKAGE');
end;
/

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;
/