建立儲存過程編譯無效物件

有時候我們需要自己編寫程式碼實現對無效物件的編譯，以下儲存過程可以完成該工作。

 

具有DBA角色的使用者在編譯和執行該儲存過程前需要顯示授予以下許可權：

grant select on dba_objects to dis;

grant execute on dbms_registry_sys to dis;

grant execute on utl_recomp to dis;

 

以下是編譯無效物件的儲存過程：

create or replace procedure recompile is

 

  -- 編譯無效物件

 

  type tab_char is table of varchar2(32767) index by binary_integer;

  commands     tab_char;

  table_exists number;

 

begin

  sys.utl_recomp.recomp_parallel(0);

 

  select count(*)

    into table_exists

    from dba_objects

   where owner = 'SYS'

     and object_name = 'UTLIRP_ENABLED_FUNC_INDEXES'

     and object_type = 'TABLE';

 

  if (table_exists > 0) then

    execute immediate q'+

         select 'alter index "' || u.name || '"."' || o.name || '" enable'

            from utlirp_enabled_func_indexes e, ind$ i, obj$ o, user$ u

            where e.obj# = i.obj# and i.obj# = o.obj# and o.owner# = u.user#

              and bitand(i.flags, 1024) != 0+' bulk

                      collect

      into commands;

 

    if (commands.count() > 0) then

      for i in 1 .. commands.count() loop

        execute immediate commands(i);

      end loop;

    end if;

 

    execute immediate 'drop table utlirp_enabled_func_indexes';

  end if;

 

  sys.dbms_registry_sys.validate_components;

end;

/