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:
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:
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:
This should return the following database banner:
Oracle Database 19c Enterprise Edition Release 220.127.116.11.0 - Production
You can also fetch the full banner version, as shown below:
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.
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:
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:
PL/SQL procedure successfully completed.
This article covered six main methods of determining the Oracle database version using various tools. Several examples were provided for each tool.