7 Ways to Check your Oracle Version

Here are seven ways to check which version of Oracle Database you’re running.

They are:

• The V$VERSION view.
• The V$INSTANCE view.
• The PRODUCT_COMPONENT_VERSION view.
• The SQL Developer GUI.
• The SQLcl tool.
• The SQL*Plus tool.
• The DBMS_DB_VERSION package.

Examples below.

The V$VERSION View

The V$VERSION view returns the version and component level information.

Example:

SELECT BANNER_FULL
FROM V$VERSION;

Result:

BANNER_FULL                                                                     
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production          
Version 19.3.0.0.0

Other columns include BANNER, BANNER_LEGACY, and CON_ID, but the BANNER_FULL column returns everything we need.

The V$INSTANCE View

If you have the appropriate permissions, you can use the V$INSTANCE view to return just the version.

Example:

SELECT 
    VERSION,
    VERSION_FULL
FROM V$INSTANCE;

Result:

VERSION           VERSION_FULL     
----------------- -----------------
19.0.0.0.0        19.3.0.0.0       

The PRODUCT_COMPONENT_VERSION View

Another option is the PRODUCT_COMPONENT_VERSION data dictionary view.

Example:

SELECT 
    VERSION,
    VERSION_FULL
FROM PRODUCT_COMPONENT_VERSION;

Result:

      VERSION    VERSION_FULL 
_____________ _______________ 
19.0.0.0.0    19.3.0.0.0      

The SQL Developer GUI

If you’re using the SQL Developer GUI, and you don’t want to run the above views (or can’t remember their names), you can navigate to the applicable data dictionary report in the Reports pane.

To do this, under the Reports pane, go to Data Dictionary Reports > About Your Database > Version Banner.

Clicking on Version Banner opens a prompt where you need to select the connection:

Select the appropriate connection and click OK.

This opens a new tab with the version information:

The SQLcl Tool

SQLcl is a command line interface for working with Oracle Database. When you use SQLcl to connect to an Oracle instance, the release and version are displayed.

Example:

sql hr/oracle

Result:

SQLcl: Release 21.2 Production on Mon Jul 12 13:23:12 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Mon Jul 12 2021 13:23:13 +10:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

The first thing returned when connecting via SQLcl is the SQLcl release number. However, once it connects, it then returns the Oracle Database release and version information.

Here, I connected using the username hr and password oracle. The sql command is the name of the executable SQLcl file, its folder of which I’ve previously added to my PATH variable. If you haven’t added it to your PATH, you may need to include the full path.

Of course, once you’ve connected with SQLcl, you can also use any of the other methods on this page to get the Oracle Database version.

The SQL*Plus Tool

If you don’t have SQLcl, you could try SQL*Plus.

Oracle SQL*Plus is a command line tool that is installed with every Oracle Database Server or Client installation. It’s been around since 1985 (a lot longer than SQLcl). It displays the same information that SQLcl displays when connecting to Oracle Database.

Example:

sqlplus hr/oracle

Result:

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 11 23:38:16 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sun Jul 11 2021 23:37:36 -04:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

The DBMS_DB_VERSION Package

Another option is the DBMS_DB_VERSION package. We can return the VERSION constant and also the RELEASE constant if required.

Example:

SET SERVEROUTPUT ON;
EXEC DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION||'.'||DBMS_DB_VERSION.RELEASE);

Result:

19.0


PL/SQL procedure successfully completed.