的呼唤

BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA(schema => '<SCHEMA_NAME>', compile_all => FALSE);
END;
/

不编译无效的包体。有人知道原因吗?

(Oracle 数据库 11g 企业版 11.2.0.1.0 版 - 生产)

最佳答案

确实如此;至少在 11.2.0.3 中,所以我想基本版本中可能存在错误。

如果我创建了一个无效的包,在这种情况下是因为它引用了一个不存在的表:

create package p42 as
  procedure test;
end p42;
/

PACKAGE P42 compiled

create package body p42 as
  procedure test is
    n number;
  begin
    select count(*) into n from t42;
  end test;
end p42;
/

PACKAGE BODY P42 compiled
Errors: check compiler log

然后检查状态和上次 DDL 时间:
select object_type, object_name, status, last_ddl_time
from user_objects where object_name = 'P42'
order by object_type, object_name;

OBJECT_TYPE         OBJECT_NAME          STATUS  LAST_DDL_TIME
------------------- -------------------- ------- -------------------
PACKAGE             P42                  VALID   2015-03-02 17:39:42
PACKAGE BODY        P42                  INVALID 2015-03-02 17:39:42

然后重新编译架构并再次检查:
BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);
END;
/

anonymous block completed

select object_type, object_name, status, last_ddl_time
from user_objects where object_name = 'P42'
order by object_type, object_name;

OBJECT_TYPE         OBJECT_NAME          STATUS  LAST_DDL_TIME
------------------- -------------------- ------- -------------------
PACKAGE             P42                  VALID   2015-03-02 17:39:42
PACKAGE BODY        P42                  INVALID 2015-03-02 17:39:49

..上次DDL时间变了,所以重新编译了。它仍然无效,因为我还没有解决根本问题。我可以看到
select text from user_errors where name = 'P42';

TEXT
------------------------------------------------------------
PL/SQL: ORA-00942: table or view does not exist
PL/SQL: SQL Statement ignored

或者,如果您的 '<SCHEMA_NAME>' 不是您当前的用户,则它将在 all_errors 中,如果它仍然无效。

如果我创建丢失的表并再次编译架构:
create table t42 (id number);

Table t42 created.

BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);
END;
/

anonymous block completed

select object_type, object_name, status, last_ddl_time
from user_objects where object_name = 'P42'
order by object_type, object_name;

OBJECT_TYPE         OBJECT_NAME          STATUS  LAST_DDL_TIME
------------------- -------------------- ------- -------------------
PACKAGE             P42                  VALID   2015-03-02 17:39:42
PACKAGE BODY        P42                  VALID   2015-03-02 17:40:11

... 上次 DDL 时间又发生了变化,现在的状态也是如此。如果我使用您的 compile_all => FALSE 标志再次编译,那么最后的 DDL 时间不会改变,因为它不会查看有效的包。

关于oracle - DBMS_UTILITY.COMPILE_SCHEMA(schema => '<SCHEMA_NAME>' , compile_all => FALSE) 不编译无效的包体,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28815914/

10-16 15:54
查看更多