Check Oracle Version

The main tool for all your administration is the information gathered from your server, starting with the server and database versions.

This tutorial will explore ways to determine your database version using Oracle SQL tools.

Method 1 – Using the SQL Developer

The first method you can use to determine your database version is using the graphical interface.

Launch the SQL Developer Utility and navigate to the Reports Pane.

Open the Data Dictionary Reports -> About Your Database -> Version Banner

Select the connection you wish to use in the pop-out window, as shown below:

Click OK on the target connection to view the database version. This should display information about the installed database version, as shown below:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Method 2 – Using the SQLPlus Utility

Oracle provides a command-line utility that allows us to query the server using PL/SQL commands.

The SQL Plus utility is simple and intuitive to use. It also comes installed in all Oracle versions making it a universal tool.

To check the database version using the SQL Plus utility, start by logging in to the server, as shown below:

$ sqlplus '/ as sysdba'

Once logged in, you should see the installed database banner displayed in the console, as shown below:

Method 3 – Using the V$VERSION VIEW

The third method you can use to determine your Oracle database version is the $VERSION view. This view stores PL/SQL information, including the database version.

To use it, we can query the banner column from the view, as shown below:

select banner from V$VERSION;

This should return the following database banner:

BANNER

----------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

You can also fetch the full banner version, as shown below:

select banner_full from V$VERSION;

Output:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.3.0.0.0

Method 4 – Using the V$INSTANCE View

Similarly, you can use the V$INSTANCE view, which stores the server instance information to determine the database version.

Example:

select version, version_full from v$instance;

This should return the following version and the full version:

NOTE: Using the v$instance view requires specific permissions to query. Instead, use the v$version view.

Method 5 – Using the Product_Component_Version

Using the product_component_version view, we can run the following:

select version, version_full from PRODUCT_COMPONENT_VERSION;

This should return a similar output to the v$instance query without the permission restrictions.

Method 6 – Using the PL/SQL DBMS_DB_VERSION

You can also use the DBMS_DB_VERSION package to determine the database version as demonstrated below:

set serveroutput on;

exec dbms_output.put_line(dbms_db_version.version||'.'||dbms_db_version.release);

Output:

19.0

PL/SQL procedure successfully completed.

Conclusion

This article covered six main methods of determining the Oracle database version using various tools. Several examples were provided for each tool.