Query for Oracle Version

This post will discuss five main methods to query for the Oracle server’s installed database version.

Method 1 – 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:

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

Method 2 – 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:

This should return the database banner as shown below:

You can also fetch the following full banner version:

Output:

Method 3 – 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:

This should return the version and full version as shown below:

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

Method 4 – Using the Product_Component_Version

Using the product_component_version view, we can run the following:

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

Method 5 – Using the PL/SQL DBMS_DB_VERSION

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

Output:

Conclusion

This post covered five methods of checking the Oracle database version using various tools. Several examples are provided for each version.