CNDBA社区
Oracle 数据库在升级操作之后我们都需要检查对应的版本信息。 在Oracle 12c 以前的版本，我们需要通过opatch命令来确认。 在Oracle 12c中，oracle 引入了Queryable Patch Inventory。 可以直接在SQL 命令中检索 patch inventory。
Queryable Patch Inventory是通过使用外部表，预处理功能和 PL/SQL 包的函数和存储过程来实现的。它采用预处理功能，允许用户预处理输入数据，然后再发送到访问驱动程序。
关于预处理的更多信息，请参阅：
Oracle 数据库通过DBMS_QOPATCH 包提供了 PLSQL/SQL 接口来查看数据库已经安装的补丁。该接口提供了 ‘opatch lsinventory -xml’ 命令所能查看的所有的补丁信息。该包实时访问 OUI patch inventory 以提供补丁和修补的元信息。
关于该包的详细说明可以参考官方手册：
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_QOPATCH.html
除了DBMS_QOPATCH之外， patch inventory 包还包含其他可查询的对象，如下：
[dave@www.cndba.cn ~]# su - oracle
Last login: Wed Feb 9 17:15:56 CST 2022 on pts/6
[dave@www.cndba.cn ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 14 20:58:31 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
SQL> select owner,object_name,object_type,status from dba_objects where object_name like '%OPATCH%';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------- ------------------------- ---------------------
SYS OPATCH_LOG_DIR DIRECTORY VALID
SYS OPATCH_SCRIPT_DIR DIRECTORY VALID
SYS OPATCH_XML_INV TABLE VALID
SYS OPATCH_XINV_TAB TABLE VALID
SYS OPATCH_INST_JOB TABLE VALID
SYS OPATCH_INST_PATCH TABLE VALID
SYS OPATCH_SQL_PATCHES TABLE VALID
SYS OPATCH_NODE_ARRAY TYPE VALID
SYS QOPATCH_LIST TYPE VALID
SYS DBMS_QOPATCH PACKAGE VALID
SYS OPATCH_INST_DIR DIRECTORY VALID
SYS LOAD_OPATCH_INVENTORY JOB VALID
SYS DBMS_QOPATCH PACKAGE BODY VALID
13 rows selected.
DBMS_QOPATCH PACKAGE 提供了如下functions and procedures ：
这些 functions/procedure 可以直接在 SQL 提示符下执行，也可以使用 PL/SQL 运行。
操作示例：
SQL> select xmltransform(dbms_qopatch.get_opatch_lsinventory(), dbms_qopatch.GET_OPATCH_XSLT()) from dual ;
XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY(),DBMS_QOPATCH.GET_OPATCH_XSLT())
--------------------------------------------------------------------------------------------------------------------------------------------
Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------
Oracle Home : /u01/app/oracle/product/19.3.0/dbhome_1
Inventory : /u01/app/oraInventory
--------------------------------------------------------------------------------Installed Top-level Products (1):
19.0.0.0.0
Installed Products ( 128)
Oracle Database 19c 19.0.0.0.0
Java Development Kit 1.8.0.201.0
……
Oracle Partitioning 19.0.0.0.0
Enterprise Edition Options 19.0.0.0.0
Interim patches:
Patch(sqlpatch) 33515361: applied on 2022-02-09T17:45:36+08:00
Unique Patch ID: 24589353
Patch Description: Database Release Update : 19.14.0.0.220118 (33515361)
Created on : 13 Jan 2022, 06:14:07 hrs UTC
Bugs fixed:
7391838 8460502 8476681 14570574 14735102 15931756 15959416 16662822 16664572 16750494 17275499 17395507 17428816 1746
8475 17777718 18534283 18697534 19080742 19138896 19697993 20007421 20083476 20313356 20319830 20479545 20867658 20922160 2111
……
SQL> select xmltransform(dbms_qopatch.get_opatch_data(29585399),dbms_qopatch.GET_OPATCH_XSLT()) from dual;
XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_DATA(29585399),DBMS_QOPATCH.GET_OPATCH_XSLT())
--------------------------------------------------------------------------------------------------------------------------------------------
Patch Information:
29585399: applied on 2019-04-18T15:21:33+08:00
SQL> select xmltransform(dbms_qopatch.get_opatch_files(29585399),dbms_qopatch.GET_OPATCH_XSLT()) from dual;
XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_FILES(29585399),DBMS_QOPATCH.GET_OPATCH_XSLT())
--------------------------------------------------------------------------------------------------------------------------------------------
Patch Id : 29585399
clustdb.sql
SQL> set long 20000
SQL> select dbms_qopatch.get_opatch_files(29585399) from dual;
DBMS_QOPATCH.GET_OPATCH_FILES(29585399)
--------------------------------------------------------------------------------------------------------------------------------------------
<patchFiles><patchID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">29585399</patchID><files xmlns:xsi="http://www.w3.org/2001/XMLSch
ema-instance"><file>clustdb.sql</file></files></patchFiles>
GET_OPATCH_XSLT 函数返回 Opatch XML inventory 的样式表。
也可以用自己定义的样式表，具体参考下面的文档：
http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb08tra.htm#ADXDB0900
How to Format XML Transform Calculation Results Using XML Transform (Doc ID 370697.1)
Oracle Database 12.1 可查询的 patch inventory（Queryable Patch Inventory） (Doc ID 1945390.1)
版权声明：本文为博主原创文章，未经博主允许不得转载。
