How to Check Oracle Database Version

How to Check Oracle Database Version | We can get the Oracle database version just by typing a select query. There are some views like V$VERSION, V$INSTANCE, and static data dictionary view PRODUCT_COMPONENT_VERSION which stores database version information.

Check Oracle Database Version Using V$VERSION

The version information of the Oracle database is stored under v$version view. V$ views are a set of views, that are dynamic in nature and always up-to-date with the server’s current state.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

It will display the database version, PL/SQL release version, and many other things. To get exactly only version information we can execute the below query,

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

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Get Oracle Database Version Using PRODUCT_COMPONENT_VERSION

Apart from the dynamic V$VERSION view, Oracle also has a static data dictionary view named PRODUCT_COMPONENT_VERSION. The view can be used like V$VERSION to get the oracle version through SQL query.

SELECT * FROM PRODUCT_COMPONENT_VERSION;

To better view we can use formats. Example:-

COL PRODUCT FORMAT A40
COL VERSION FORMAT A15
COL STATUS FORMAT A16
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION         STATUS
---------------------------------------- --------------- ----------------
NLSRTL                                   11.2.0.1.0      Production
Oracle Database 11g Enterprise Edition   11.2.0.1.0      64bit Production
PL/SQL                                   11.2.0.1.0      Production
TNS for 64-bit Windows:                  11.2.0.1.0      Production

Find Oracle Database Version Using V$INSTANCE

If user have dba privilege then user can access database version information in V$INSTANCE view.

SQL> SELECT version from V$INSTANCE;

VERSION
---------------
11.2.0.1.0

Another way,

SQL> SET SERVEROUTPUT ON
SQL> EXEC dbms_output.put_line( dbms_db_version.version );

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or you find anything incorrect? Let us know in the comments. Thank you!

Others

Leave a Reply