CODEWITHSUNDEEP
sqloracle-databaseversion
Igor
Igor

Reputation: 6245

Query to get Oracle DB version

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?

Upvotes: 24

Views: 41386

Answers (3)

Thomas Kirchhoff
Thomas Kirchhoff

Reputation: 978

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

Upvotes: 38

EdStevens
EdStevens

Reputation: 3872

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.

Upvotes: 11

ramlalsharma1994
ramlalsharma1994

Reputation: 158

SELECT * FROM v$version WHERE banner LIKE 'Oracle%';

Upvotes: 1

Related Questions