ORA-04061:程序包主体"PACKAGE.NAME"的现有状态已失效持续 [英] ORA-04061: existing state of package body "PACKAGE.NAME" has been invalidated persists

问题描述

在我正在处理的一个Oracle数据库实例上，我在重新编译软件包时观察到与正常行为不同的情况.

On one of Oracle DB instances I am working on I am observing a different than normal behavior when recompiling packages.

通常，(如有问题的

Typically, (as in question Frequent error in Oracle ORA-04068: existing state of packages has been discarded) following error on first call is expected after PL/SQL package recompilation:

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "PACKAGE.NAME" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"PACKAGE.NAME"
ORA-06512: at line 1

但是，假设该程序包没有错误，则第二次调用应该可以正常工作.此行为以前在该环境中存在.同时，我们从11g R2升级到12c R1，并启用了基于版本的重新定义.

But the second call should work fine, assuming the package has no errors of course. This behavior was present previously in that environment. In the meantime we upgraded from 11g R2 to 12c R1, and enabled edition based redefinition.

现在，我遇到的问题是我一直处于公正状态:

Now the problem I am experiencing is that I keep getting just:

ORA-04061: existing state of package body "PACKAGE.NAME" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"PACKAGE.NAME"
ORA-06512: at line 1

因此，不再有ORA-04068，解决此问题的唯一方法是重新连接会话或手动调用DBMS_SESSION.RESET_PACKAGE()(但无论如何我都无法控制所有可能受到影响的代码)，否则问题仍然存在每个电话.

So no ORA-04068 anymore, and only way to fix it is to reconnect the session or calling DBMS_SESSION.RESET_PACKAGE() manually (but I don't control all code that may be affected anyway), otherwise the problem persists on every call.

是否有任何数据库参数可以对此进行调整?该问题并不特定于任何特定的PL/SQL软件包，并且当它引用，更改某些内容时，似乎可以由正常的软件包失效触发.

Are there any DB parameters that control this that could got tweaked? The problem is not specific to any particular PL/SQL package and it seems that it can be triggered by normal package invalidation when something it references, changes.

谢谢.

推荐答案

Oracle之所以这样做，是因为重新编译PL/SQL包会使使用中的所有会话变量无效.

Oracle does this because recompiling a PL/SQL package invalidates any session variables in use.

除了采用良好的部署实践之外，我们无法做很多事情来避免这种情况.在使用数据库时不要部署更改，请确保所有连接都正确断开，等等.比起CI/CD时代，零停机时间和其他令人振奋的创新，这种说法要容易得多.

There isn't much we can do to avoid this, except by using good deployment practices. Don't deploy changes while the database is in use, make sure all the connections are properly disconnected, etc. Easier said than done in this age of CI/CD, zero downtime and other exciting innovations.

因此，在储物柜的背面有一件东西:pragma serially_reusable;.此说明表示在单个服务器调用期间，软件包的状态将一直保持.例如，如果我们有一个PL/SQL块来调用SR过程三倍，那么该过程所更改的任何变量将在三个调用中保持该值.但是，下次我们在同一会话中运行该块时，这些变量将被重置为其初始值.

So there is one thing in the back of the locker: pragma serially_reusable;. This instruction means the package's state is maintained for the duration of a single server call. For instance if we have a PL/SQL block which calls an SR procedure three times any variables altered by that procedure will main the value across the three calls. But the next time we run the block - in the same session - the variables will have been reset to their starting values.

可串行重用的PL/SQL有几个限制-例如，它不能在SQL查询中使用.但是从您的角度来看，最大的吸引力就是不再出现ORA-04068或ORA-04061错误.没有会话状态，没有要失效的东西.

There are several limitations to serially reusable PL/SQL - for instance, it can't be used in SQL queries. But the big attraction from your perspective is no more ORA-04068 or ORA-04061 errors. No session state, nothing to invalidate.

pragma serially_reusable必须在包级别，主体以及规范中声明.因此，您必须确保所有打包过程都不需要维护服务器调用之间的状态.

pragma serially_reusable must be declared at the package level, and in the body as well as the spec. So you must be sure that none of the packaged procedures need to maintain state across server calls.

这篇关于ORA-04061:程序包主体"PACKAGE.NAME"的现有状态已失效持续的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！