Oracle 19c中获取当前数据库版本
背景描述:在日常运维过程中，多种场景需要数据库的版本，本文总结了Oracle中通过不同方式获取数据库版本的方法。
适用范围：Oracle 19c
操作过程：
1、 sqlplus命令行方式
sqlplus 命令行工具能以最直接的方式获取数据库版本信息
[oracle@db19do01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 10 20:10:35 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>2、查询v$version视图
这个视图提供了数据库的标志，并根据数据库版本提供了一些关于核心数据库组件的附加信息，可以在数据库处于NOMOUNT状态时使用。从18c开始，添加了列BANNER_FULL，并包含多行字符串，其中还包括发布更新版本号。
SQL> select BANNER from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>
SQL> SELECT banner_full FROM v$version;
BANNER_FULL
-------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
nomount
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1157627896 bytes
Fixed Size 9134072 bytes
Variable Size 503316480 bytes
Database Buffers 637534208 bytes
Redo Buffers 7643136 bytes
SQL> select open_mode from v$database;
select open_mode from v$database *
ERROR at line 1:
ORA-01507: database not mounted
SQL> show pdbs
SQL> SELECT version, version_legacy, version_full FROM v$instance;
VERSION VERSION_LEGACY VERSION_FULL
-------------------- ----------------- --------------------
19.0.0.0.0 19.0.0.0.0 19.3.0.0.0
SQL> SELECT banner_full FROM v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>2、 查询PRODUCT_COMPONENT_VERSION
从18c开始，VERSION_FULL显示包含已安装版本更新的版本。
SELECT version, version_full FROM product_component_version;
SQL> col version for a20
SQL> col version_full for a20
SQL> SELECT version, version_full FROM product_component_version;
VERSION VERSION_FULL
-------------------- --------------------
19.0.0.0.0 19.3.0.0.03、查询v$instance视图
SQL> desc v$instance
Name Null? Type
----------------------------------------- -------- ----------------------------
INSTANCE_NUMBER NUMBER
INSTANCE_NAME VARCHAR2(16)
HOST_NAME VARCHAR2(64)
VERSION VARCHAR2(17)
VERSION_LEGACY VARCHAR2(17)
VERSION_FULL VARCHAR2(17)
STARTUP_TIME DATE
STATUS VARCHAR2(12)
PARALLEL VARCHAR2(3)
THREAD# NUMBER
ARCHIVER VARCHAR2(7)
LOG_SWITCH_WAIT VARCHAR2(15)
LOGINS VARCHAR2(10)
SHUTDOWN_PENDING VARCHAR2(3)
DATABASE_STATUS VARCHAR2(17)
INSTANCE_ROLE VARCHAR2(18)
ACTIVE_STATE VARCHAR2(9)
BLOCKED VARCHAR2(3)
CON_ID NUMBER
INSTANCE_MODE VARCHAR2(11)
EDITION VARCHAR2(7)
FAMILY VARCHAR2(80)
DATABASE_TYPE VARCHAR2(15)
SQL> SELECT version, version_legacy, version_full FROM v$instance;
VERSION VERSION_LEGACY VERSION_FULL
-------------------- ----------------- --------------------
19.0.0.0.0 19.0.0.0.0 19.3.0.0.0
SQL>nomount状态下：
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1157627896 bytes
Fixed Size 9134072 bytes
Variable Size 503316480 bytes
Database Buffers 637534208 bytes
Redo Buffers 7643136 bytes
SQL> select open_mode from v$database;
select open_mode from v$database *
ERROR at line 1:
ORA-01507: database not mounted
SQL> show pdbs
SQL> SELECT version, version_legacy, version_full FROM v$instance;
VERSION VERSION_LEGACY VERSION_FULL
——————– —————– ——————–
19.0.0.0.0 19.0.0.0.0 19.3.0.0.0
SQL>
4、查询dba_registry和cba_registry
SQL> SELECT version, version_full FROM dba_registry
WHERE comp_id = 'CATALOG';
VERSION VERSION_FULL
-------------------- --------------------
19.0.0.0.0 19.3.0.0.0
SQL> SELECT version, version_full,CON_ID FROM cdb_registry
2 WHERE comp_id = 'CATALOG';
VERSION VERSION_FULL CON_ID
-------------------- -------------------- ----------
19.0.0.0.0 19.3.0.0.0 1
19.0.0.0.0 19.3.0.0.0 3
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XFPDB READ WRITE NO
SQL>5、通过调用PL/SQL 方法
5.1 DBMS_DB_VERSION
这个包没有过程或函数，但是提供了获取版本和发布号的常量。常量只能在PL/SQL块中使用。用这个包是不可能得到补丁集或发布更新版本号。
SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE);
19.0
PL/SQL procedure successfully completed.
SQL>5.2 DBMS_UTILITY
在PL/SQL块过程中，DB_VERSION可以用于提取当前数据库版本和当前设置的兼容版本。
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
v_version VARCHAR2(50);
v_compatibility VARCHAR2(50);
BEGIN
DBMS_UTILITY.DB_VERSION (
version => v_version,
compatibility => v_compatibility
);
DBMS_OUTPUT.PUT_LINE('Version: ' || v_version);
DBMS_OUTPUT.PUT_LINE('Compatibility: ' || v_compatibility);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13
Version: 19.0.0.0.0
Compatibility: 19.0.0
PL/SQL procedure successfully completed.
SQL>
-the end-