Learn how to check the installed version of oracle Database with example queries.
Below are the queries to know the release version of oracle database that is currently installed in your PC.
Query 1: All Version Information using v$version
To retrieve all version information from Oracle, you could execute the following SQL statement:
SELECT * FROM v$version;
| S.N. | BANNER | CON_ID |
| 1 | Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production | 0 |
| 2 | PL/SQL Release 12.1.0.2.0 – Production | 0 |
| 3 | CORE 12.1.0.2.0 Production | 0 |
| 4 | TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 – Production | 0 |
| 5 | NLSRTL Version 12.1.0.2.0 – Production | 0 |
V$VERSION displays version numbers of core library components in Oracle.
Query 2: details of the current database instance using V$INSTANCE table
SELECT version FROM V$INSTANCE
| VERSION | |
| 1 | 12.1.0.2.0 |
V$INSTANCE shows details of the current database instance such as host name, startup time, status, etc. The column VERSION in V$INSTANCE contains the database version.
SELECT * FROM V$INSTANCE
| INSTANCE_NUMBER | INSTANCE_NAME | HOST_NAME | VERSION | STARTUP_TIME | STATUS | PARALLEL | THREAD# | ARCHIVER | LOG_SWITCH_WAIT | LOGINS | SHUTDOWN_PENDING | DATABASE_STATUS | INSTANCE_ROLE | ACTIVE_STATUS | BLOCKED | CON_ID | INSTANCE_MODE | EDITION | FAMILY | |
| 1 | 1 | database | IJPRIBME850-09-CL6-REVUAT | 12.1.0.2.0 | 12/5/2018 7:58:04 PM | OPEN | NO | 1 | STOPPED | ALLOWED | NO | ACTIVE | PRIMARY_INSTANCE | NORMAL | NO | 0 | REGULAR | EE |
DBMS_DB_VERSION
The package contains two constants that specify the database version and release number. They are:
dbms_db_version.version
dbms_db_version.release
Query 3: details of the current database version and release.
BEGIN
DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE);
END;
Output
12.1