验证 SQL 补丁历史、组件状态和无效对象

命令

-- 1) datapatch history (most recent at the bottom):
SELECT patch_id, patch_type, action, status, source_version, target_version,
       TO_CHAR(action_time,'YYYY-MM-DD HH24:MI') AS action_time, description
FROM   dba_registry_sqlpatch
ORDER  BY action_time;

-- Failed entries and their log files:
SELECT install_id, patch_id, action, status, logfile
FROM   dba_registry_sqlpatch
WHERE  status <> 'SUCCESS';

-- In a CDB, for all PDBs (from root):
SELECT con_id, patch_id, action, status
FROM   cdb_registry_sqlpatch ORDER BY con_id, action_time;

-- 2) Components must be VALID:
SELECT comp_id, comp_name, version_full, status
FROM   dba_registry ORDER BY comp_id;

-- 3) Invalid object summary (compare before/after utlrp):
SELECT owner, object_type, COUNT(*) AS invalid_cnt
FROM   dba_objects
WHERE  status = 'INVALID'
GROUP  BY owner, object_type
ORDER  BY invalid_cnt DESC;

作用

一目了然地显示打补丁后 SQL 层是否真正更新（数据字典与二进制文件一致）、哪个补丁在哪个 PDB 中失败、数据字典组件是否为 VALID，以及无效对象的数量。

适用场景

作为每次 datapatch 后的验证步骤、在打补丁或升级前捕获基线，以及插入 PDB 后怀疑存在不一致时。

注意

只读；无需许可。SOURCE_VERSION/TARGET_VERSION 在 18c+ RU 模型中填充。如果 STATUS='WITH ERRORS'，请修复 LOGFILE 中的错误并重新运行 datapatch。部分组件（例如未使用的 XDB/JAVA）可能显示 INVALID/REMOVED；请与补丁前的状态对比。