I'm looking for a way to get an Oracle DB Server version as "X.Y.Z".
I can use a query from here, but it will show a version of not just a server.
I can use v$version view but the resulting string will contain much more than just "X.Y.Z".
Is there a simple way to get what I want?
SQL
SELECT version FROM PRODUCT_COMPONENT_VERSION
WHERE product LIKE 'Oracle Database%';
Result:
VERSION
----------
18.0.0.0.0
18c/19c: VERSION_FULL also shows the installed release update
View V$INSTANCE (Requires SELECT privilege on SYS.V_$INSTANCE):
SELECT version FROM v$instance;
DBA_REGISTRY (Requires SELECT privilege on SYS.DBA_REGISTRY):
Shows the version for each installed component.
SELECT * FROM dba_registry
WHERE comp_id = 'CATALOG';
PL/SQL
DECLARE
l_version VARCHAR2(20);
l_compatibility VARCHAR2(20);
BEGIN
DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
DBMS_OUTPUT.PUT_LINE(l_version);
END;
/
Result:
18.0.0.0.0
Still shorter:
BEGIN
DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION|| '.' || DBMS_DB_VERSION.RELEASE);
END;
/
Result:
18.0
Why not just query v$instance?
SQL> select version from v$instance;
VERSION
-----------------
12.2.0.1.0
No need to muck around with pl/sql or called procedures.
SELECT * FROM v$version WHERE banner LIKE 'Oracle%';
Query:
SELECT banner FROM v$version WHERE banner LIKE 'Oracle Database%'
Output:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
