hnakamur’s blog

ものすごい勢いで忘れる私のために未整理でもいいからとりあえずメモ

2011-04-24

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

ブログ アーカイブ