有时候我们需要自己编写代码实现对无效对象的编译,以下存储过程可以完成该工作。
具有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/