如何以编程方式找到当前的 Oracle 补丁集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21235431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can one programmatically find the current Oracle patch set?
提问by Ben
In Identifying Your Oracle Database Software ReleaseOracle states that you can find your "platform-specific release number" (patch set) by querying PRODUCT_COMPONENT_VERSION:
在确定您的 Oracle 数据库软件版本中，Oracle 声明您可以通过查询PRODUCT_COMPONENT_VERSION来找到您的“特定于平台的版本号”（补丁集）：
To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION.
要确定当前安装的 Oracle 数据库的版本并查看您正在使用的其他数据库组件的版本级别，请查询数据字典视图 PRODUCT_COMPONENT_VERSION。
According to this we are using 11.2.0.3.0
据此我们使用 11.2.0.3.0
SQL> select * from product_component_version;
PRODUCT VERSION STATUS
----------------------------------- --------------- ---------------
NLSRTL 11.2.0.3.0 Production
Oracle Database 11g 11.2.0.3.0 64bit Production
PL/SQL 11.2.0.3.0 Production
TNS for Linux: 11.2.0.3.0 Production
The same occurs with V$VERSION (which PRODUCT_COMPONENT_VERSION is a view of incidentally):
V$VERSION 也会发生同样的情况（其中 PRODUCT_COMPONENT_VERSION 是一个视图）：
SQL> select * from v$version;
BANNER
---------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
However, according to DBA_REGISTRY_HISTORYthe database appears to be on 11.2.0.3.51:
但是，根据DBA_REGISTRY_HISTORY数据库似乎在 11.2.0.3.5 1 上：
SQL> select action, namespace, version, id, comments from dba_registry_history;
ACTION NAMESPACE VERSION ID COMMENTS
--------------- --------- ---------- ---------- ------------------------------
VIEW INVALIDATE 8289601 view invalidation
UPGRADE SERVER 11.2.0.3.0 Upgraded from 11.2.0.1.0
APPLY SERVER 11.2.0.3 0 Patchset 11.2.0.2.0
APPLY SERVER 11.2.0.3 5 PSU 11.2.0.3.5
DBA_REGISTRY_HISTORY doesn't necessarily have any data in it so I can't reliably use this view. And, Oracle doesn't seem to provide a standardised method of populating the comments field I seem to be left doing the following and then praying that it works.
DBA_REGISTRY_HISTORY 中不一定包含任何数据，因此我无法可靠地使用此视图。而且，Oracle 似乎没有提供填充评论字段的标准化方法，我似乎只能做以下工作，然后祈祷它有效。
select max(regexp_replace(comments, '[^[:digit:].]'))
keep (dense_rank first order by action_time desc)
from dba_registry_history
Is there an easier, reliable, method of finding out the current version, including patch set, programmatically?
是否有一种更简单、可靠的方法来以编程方式查找当前版本，包括补丁集？
1. Also possible: I'm completely misreading this and people have forgotten what they patched.
1. 也有可能：我完全误读了这个，人们忘记了他们修补了什么。
回答by Ben
As I cannot guarantee that DBA_REGISTRY_HISTORY will be populated even though it seemsto give the correct patch set I've ended up doing the following to populate from V$VERSION if there is nothing.
由于我不能保证 DBA_REGISTRY_HISTORY 会被填充，即使它似乎提供了正确的补丁集，如果没有任何东西，我最终会执行以下操作以从 V$VERSION 填充。
with drh as (
select max(regexp_replace(comments, '[^[:digit:].]'))
keep (dense_rank last order by action_time) as vers
from dba_registry_history
)
, v$v as (
select regexp_substr(banner, '(\d+\.?){5}', 1) as vers
from v$version
where lower(banner) like 'oracle%'
)
select coalesce(drh.vers, v$v.vers) as patch_set
from drh
right outer join v$v
on 1 = 1
This works because both queries will only return one row and I've tested it on 10.2, 11.2 and 12.1
这是有效的，因为两个查询都只会返回一行，我已经在 10.2、11.2 和 12.1 上对其进行了测试
It is, however, ridiculous and ugly. There's no guarantees that it won't break as everything's a free-text field and Oracle seems to change how it displays the data in these views occasionally. Also, Oracle isn't even consistent within these views. Here's a 12c database, notice the comments field magically reverts the patch set when being upgraded, and how the version and comments don't match.
然而，这是可笑和丑陋的。无法保证它不会中断，因为所有内容都是自由文本字段，而且 Oracle 似乎偶尔会更改在这些视图中显示数据的方式。此外，Oracle 在这些视图中甚至不一致。这是一个 12c 数据库，注意注释字段在升级时神奇地恢复了补丁集，以及版本和注释如何不匹配。
SQL> select action, version, id, comments from dba_registry_history;
ACTION VERSION ID COMMENTS
--------------- ---------- -------- ------------------------
APPLY 11.2.0.3 0 Patchset 11.2.0.2.0
APPLY 11.2.0.3 0 Patchset 11.2.0.2.0
APPLY 11.2.0.3 5 PSU 11.2.0.3.5
VIEW INVALIDATE 8289601 view invalidation
UPGRADE 12.1.0.1.0 Upgraded from 11.2.0.3.0
APPLY 12.1.0.1 0 Patchset 12.1.0.0.0
6 rows selected.
So, it'd still be nice if this data was exposed in a manner that's easy to use.
因此，如果以易于使用的方式公开这些数据仍然会很好。
回答by pooh06
-- list applied patches using sqlplus
-- 使用 sqlplus 列出应用的补丁
SET linesize 200 pagesize 200
col action_time FOR a28
col version FOR a10
col comments FOR a35
col action FOR a25
col namespace FOR a12
SELECT * FROM registry$history;
-- list applied patches using opatch
-- 使用 opatch 列出应用的补丁
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch lsinventory|grep "Patch description"
$ORACLE_HOME/OPatch/opatch lsinventory|grep "补丁说明"
$ORACLE_HOME/OPatch/opatch lsinventory -details
$ORACLE_HOME/OPatch/opatch lsinventory -details
