cm如何查看各个组件版本_查询Oracle各组件的版本信息

有朋友在ITPUB上提问，如何查询Oracle各组件的版本信息。

这里想说说其他的几个视图。有朋友提到了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 Production

SQL> 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, ' - '))

很多东西只要深入一点点，就能发现问题的本质。

是不是很好玩呢?