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>
3、 查询 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.0
4、查询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>
5、查询dba_registry和cdb_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>
6、通过 调用PL/SQL 方法
这个包没有过程或函数，但是提供了获取版本和发布号的常量。常量只能在 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>
6.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-
