查詢Oracle各組件的版本信息

2024-08-29 13:34:25

字體：大中小

來源：轉載

供稿：網友

有朋友在ITPUB上提問，如何查詢Oracle各組件的版本信息。其實這個問題，最好的方法是用opatch工具，可以查詢得到具體的安裝組件版本。
這個可以參考我的網站鏈接:
http://www.eygle.com/internal/Use.Opatch.apply.Interim.Patch.htm 這里想說說其他的幾個視圖。有朋友提到了v$version 和 PRodUCt_component_version 兩個視圖。
我們來看一下這兩者有意思的關系.首先看看兩者的輸出:

SQL> select * from PRODUCT_COMPONENT_VERSION; PRODUCT VERSION STATUS
------------------------------ -------------------- ----------
NLSRTL 9.2.0.4.0 Production
Oracle9i Enterprise Edition 9.2.0.4.0 Production
PL/SQL 9.2.0.4.0 Production
TNS for linux: 9.2.0.4.0 ProductionSQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

似乎不同，是么？
那我們再來看看這兩者的來源，可以借用一下sqlplus的autotrace功能:

SQL> set autotrace trace eXPlain
SQL> select * from v$version; Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FIXED TABLE (FULL) OF 'X$VERSION'SQL> select * from PRODUCT_COMPONENT_VERSION;Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW OF 'PRODUCT_COMPONENT_VERSION'
2 1 SORT (UNIQUE)
3 2 UNION-ALL
4 3 FIXED TABLE (FULL) OF 'X$VERSION'
5 3 FIXED TABLE (FULL) OF 'X$VERSION'

我們可以發現，這兩者都來自底層表x$version，也就是說這兩個視圖同源，只不過展現有所不同而已。查詢一下x$version的基本信息:

SQL> col indx for 9999
SQL> col inst_id for 99
SQL> select * from x$version; ADDR INDX INST_ID BANNER
-------- ----- ------- ----------------------------------------------------------------
B701CDCC 0 1 Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
B701CDCC 1 1 PL/SQL Release 9.2.0.4.0 - Production
B701CDCC 2 1 CORE 9.2.0.3.0 Production
B701CDCC 3 1 TNS for Linux: Version 9.2.0.4.0 - Production
B701CDCC 4 1 NLSRTL Version 9.2.0.4.0 - Production

v$version顯然是較為單純的View，那么PRODUCT_COMPONENT_VERSION經過怎樣的處理呢？
我們看一下這個視圖的創建語句，學習一下Oracle的SQL技術：

CREATE OR REPLACE VIEW SYS.product_component_version (product, VERSION,
status)
AS
(SELECT SUBSTR (banner, 1, INSTR (banner, 'Version') - 1),
SUBSTR (banner,
INSTR (banner, 'Version') + 8,
INSTR (banner, ' - ') - (INSTR (banner, 'Version') + 8)
),
SUBSTR (banner, INSTR (banner, ' - ') + 3)
FROM v$version
WHERE INSTR (banner, 'Version') > 0
AND ( (INSTR (banner, 'Version') < INSTR (banner, 'Release'))
OR INSTR (banner, 'Release') = 0
))
UNION
(SELECT SUBSTR (banner, 1, INSTR (banner, 'Release') - 1),
SUBSTR (banner,
INSTR (banner, 'Release') + 8,
INSTR (banner, ' - ') - (INSTR (banner, 'Release') + 8)
),
SUBSTR (banner, INSTR (banner, ' - ') + 3)
FROM v$version
WHERE INSTR (banner, 'Release') > 0
AND INSTR (banner, 'Release') < INSTR (banner, ' - '))