dbms_utility.compile_schema in procedure程序

手动尝试-这样你也可以调试它。

SELECT 'alter ' 
  || DECODE( object_type,'PACKAGE BODY','PACKAGE','TYPE BODY','TYPE', object_type) 
  || ' ' 
  || object_name 
  || ' compile ' 
  ||DECODE( object_type,'PACKAGE BODY','body','TYPE BODY','body') 
  ||';' 
FROM all_objects 
WHERE owner ='schema_name' 
AND status !='VALID' ;

然后换一个伤残者。

如果有人使用架构对象,则在释放这些对象之前无法编译它们。看起来像是“无限循环”- 什么都没发生 .

当没有人工作时,即在 远离的 小时(晚上?).

可以通过重复以下SQL语句来监视重新编译无效对象:

SELECT COUNT(*) "NumberOfInvalidObject"
FROM DBA_OBJECTS
WHERE OWNER='Your_desired_schema_name_here' AND STATUS='INVALID';

将该代码放入sql developer并按f5键。你会发现无效对象的数量正在减少…

我在别的地方找到了答案。正如有人指出的,只需要编译视图,因此,下面是代码(工作),这是dbms_utility.compile_schema的替代方案:

create or replace PROCEDURE COMPILE_VIEWS AS 

  cursor OBJECTS is
  select object_name, object_type, status
  from USER_OBJECTS
  where object_type in ('VIEW')
    and object_name != 'COMPILE_SCHEMA'
    and status = 'INVALID'
  order by object_name;
  --
  cmd            varchar2(300);
  msg            varchar2(2000);
  COMPILE_ERROR  exception;
  pragma EXCEPTION_INIT (COMPILE_ERROR, -24344);
begin
  dbms_output.enable(1000000);

  for o in objects loop
        cmd := 'alter '||o.object_type||' '||o.object_name||' compile';
      begin
        execute immediate cmd;
        msg := rpad('Compiled '||lower(o.object_type),25)||o.object_name;
      exception 
        when COMPILE_ERROR then
          msg := rpad('Compiled '||lower(o.object_type),25)||rpad(o.object_name,35)||' COMPILE ERROR';        
        when OTHERS then
          msg := rpad('Compiled '||lower(o.object_type),25)||rpad(o.object_name,35)||sqlerrm;
      end;
      dbms_output.put_line(rtrim(msg));
  end loop;

END COMPILE_VIEWS;

然后运行它:

begin
  COMPILE_VIEWS();
end;

很有魅力。