How to Check Oracle Database Version

Oracle Database

➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031

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 do you find anything incorrect? Let us know in the comments. Thank you!

Others

Leave a Comment Cancel Reply