创建存储过程编译无效对象

有时候我们需要自己编写代码实现对无效对象的编译，以下存储过程可以完成该工作。

 

具有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;

/

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28974745/viewspace-2141525/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28974745/viewspace-2141525/