How to Check the Oracle Version

Knowing the version of your Oracle Database is important. It helps you evaluate support options, apply necessary updates for security and stability, and take advantage of new features to meet changing business needs. What’s more, database vulnerabilities can vary between versions, so it’s better to stay up-to-date to avoid any issues. Being aware of the version can also ensure compatibility with applications and tools, making it easier to work with the database.

In the article, we’ll review various methods to check the version of an Oracle database using a range of techniques, including commands, tools, utilities, and command-line operations to suit your preferences.

In Oracle, a version is a release of the database (for example, 19.3) and is available in several editions, such as Express or Standard.

Contents

• Why do you need to know your Oracle version?
• Checking the installation of the Oracle client from the command line

• Easiest methods to check a version
  • Using SQL*Plus
  • Querying V$INSTANCE view
  • Using dbForge Studio for Oracle
• Intermediate methods to check a version
  • Using Enterprise Manager
  • Checking the Oracle alert log
  • Using DBMS_UTILITY.DB_VERSION
• Advanced methods to check a version
  • Using PL/SQL
  • Using the oraversion utility

Why do you need to know your Oracle version?

First, let us discuss a few cases why it is important to know the Oracle version you use:

• Security: New versions often come with updates to fix security issues and protect against cyberattacks. Keeping up with these updates can ensure your database remains secure from potential threats.
• Performance: Newer versions can make your database run faster and more efficiently. They might have features that help use resources better and reduce delays.
• Feature enhancements: Each new version of Oracle Database brings new tools and functions that can make your work easier and more productive.
• Compatibility: Staying up-to-date with your Oracle version ensures it works well with the latest software and tools so you can easily connect it to other programs and services.
• Support and maintenance: Oracle provides support and fixes for its database software. But as versions get older, they might stop getting updates. By using newer versions, you can get help from Oracle when needed.

So, knowing your Oracle Database version is important to keep your data safe, make your database work better, access new tools, and guarantee it plays well with other software.

Checking the installation of the Oracle client from the command line

Before we start, verify that the Oracle client is installed on the machine. If you use Unix/Linux, open the terminal window. For Windows users, open the Command Prompt. Then, execute the sqlplus command. If it is recognized, it means that the Oracle client is installed on your machine. However, if the command is not recognized, verify whether the ORACLE_HOME environment variable is set:

• Windows: Navigate to System > Advanced System Settings. In the System Properties dialog, click Environment Variables and check for ORACLE_HOME.
• Linux/Unix: Open the terminal and type echo $ORACLE_HOME.

If the ORACLE_HOME variable is not found, download and install the Oracle client from the official website.

To proceed, let us explore the ways to check an Oracle database version.

Easiest methods to check a version

Now, let’s view some methods to get information about the Oracle database version. You can run the commands to be discussed below using SQL*Plus utility, Command Prompt, dbForge Studio for Oracle, or any other SQL client tool.

Using SQL*Plus

SQL*Plus is a command-line utility introduced by Oracle Corporation to work with Oracle databases. It is commonly used for tasks such as querying database tables, creating and modifying database objects, and managing database users and privileges.

To view the Oracle version, query the following view:

SELECT * FROM v$version

The query retrieves information about the Oracle database version you are currently connected to. The output includes the Oracle database version number, release, edition, and other relevant information.

In the context of the query, the banner columns display the following information:

• BANNER shows the edition and the basic information about the Oracle Database version,
• BANNER_FULL shows the full release information.
• BANNER_LEGACY may show an older or legacy version banner format, which might be used for compatibility or historical reasons. It could include information similar to the BANNER and BANNER_FULL columns but formatted differently.

Note that the server version is also displayed when you connect to the database using the SQL*Plus utility.

Querying the V$INSTANCE view

Another way to check the Oracle version involves querying the V$INSTANCE view. This returns information about the instance running on the database server. The query result displays the instance name, database version, start time of the instance, etc. Although the V$INSTANCE view does not typically include columns named version or version_full (especially in versions older than 18c), we can explicitly select them in the query to obtain version-related details.

Note that some permissions and privileges may be required to query the v$Instance view, including:

• SELECT privilege on the V$INSTANCE view or a role that grants the SELECT privilege on the view.
• Some columns in dynamic performance views may contain sensitive information. So, access to these columns may be restricted, requiring users to have additional privileges to query them.

The syntax of the view is as follows:

SELECT version, version_full FROM v$instance;

where version displays the version information in the format “major release.minor release.patch level”, and version_full usually includes details such as the Oracle Database edition (for example, Enterprise Edition), the specific release version (for example, 19c), the release number, and additional information about the production status.

Let’s now execute this view in dbForge Studio for Oracle, which is an all-encompassing integrated development environment (IDE) for Oracle database development and management tasks. With a suite of robust features and tools, SQL editing, visual design tools, data management functionalities, debugging utilities, version control integration, security management, reporting tools, and PL/SQL Formatter, it offers a complete solution for Oracle professionals.

You can download the Studio from the Devart website and install it on your machine. Then, open the Studio. On the toolbar, click New SQL to open a SQL editor. It helps you simplify, optimize, and autocomplete your code with the required elements without the need to memorize code details. In addition, you can improve readability of your PL/SQL code using the smart PL/SQL Formatter, which formats and beautifies your code effortlessly.

Once done, execute the query and see the result:

Using dbForge Studio for Oracle

You can quickly check the Oracle server version you are connected to when working with dbForge Studio for Oracle. Here are three ways to do this.

Method 1

Open the Studio. On the ribbon, select Database > New Connection. In the Database Connection Properties dialog that opens, enter the connection details and click Test Connection. The pop-up window about successful connection opens, displaying the version.

You can also view the version for the already established connection. To do this, in Database Explorer, right-click the required connection and select Modify Connection. The Database Connection Properties dialog appears – click Test Connection to see the version.

Method 2

On the toolbar, click New SQL. In the SQL document that opens, the server version is displayed at the bottom of the document. This information will also be shown for any SQL document you work in.

Method 3

If you want to know the version of the server you’ve already connected, in Database Explorer, right-click the required connection and select Properties.

Intermediate methods to check a version

In this section, we’ll explore the following methods to get Oracle version information:

• Using Enterprise Manager
• Checking the Oracle Alert Log
• Using DBMS_UTILITY.DB_VERSION procedure

Using Enterprise Manager 

You can easily check the server version using Oracle Enterprise Manager. It is a web-based management tool for managing and monitoring Oracle software environments.

Open a web browser and enter the URL of your Oracle Enterprise Manager Console. Then, log in with the appropriate credentials and navigate to the Targets menu. There, select the server for which you want to view version information. On the properties or general information page, you can find relevant details, including the Oracle Database version.

Checking the Oracle alert log

An alert log is a text file that records information about database events, as well as error messages and exceptions that occur. After each startup of the instance, new information is added to the alert_<SID>.log file. It is stored in the diagnostic directory of your Oracle database instance.

You can query the V$DIAG_INFO view to find the location of the database log file and read the server version. To do this, execute the following query:

SELECT * FROM V$DIAG_INFO;

The output displays the following result, including a server version:

Using DBMS_UTILITY.DB_VERSION

DBMS_UTILITY.db_version is an alternative to obtain the version details from v$version, compared to basic SQL commands. To obtain the server version, execute the following query:

SET SERVEROUTPUT ON
DECLARE
  l_version  VARCHAR2(100);
  l_compatibility  VARCHAR2(100);
BEGIN
  DBMS_UTILITY.db_version (version       => l_version,
                           compatibility => l_compatibility);
  DBMS_OUTPUT.put_line('Version: ' || l_version || '  Compatibility: ' || l_compatibility);
END;
/

The output returns the server to which the database is connected.

Advanced methods to check a version

Let’s now look at some advanced ways to determine a database version:

• Using PL/SQL
• Using the oraversion utility

Using PL/SQL

A PL/SQL block is a structured section of code that performs specific tasks. It usually consists of variable declarations, control structures (like loops and conditional statements), and executable statements.

To retrieve the version, execute the following code within a PL/SQL block:

begin
  dbms_output.put_line(
         dbms_db_version.version 
      || '.'
      || dbms_db_version.release
                      ); 
end;

The query returns the version (19) and release number (0) retrieved with the help of the dbms_db_version package that contains two constants specifying the version and release.

Using the oraversion utility

Starting with Oracle 18c, Oracle introduced the oraversion utility. It is a command-line tool that provides a quick way to get the database version.

In the Command Prompt or terminal, execute the following command to display the version:

oraversion -baseVersion

Once done, the version of the server is displayed.

Conclusion

To sum up, there are multiple ways to quickly get a database version that can suit every taste. This becomes particularly useful in instances where the version may not be the latest, prompting users to regularly update their software to mitigate compatibility and performance issues.

To top it off, download a trial version of dbForge Studio for Oracle, available for 30 days at no cost. Explore its outstanding features and PL/SQL developer tools firsthand, experiencing the seamless execution of database operations within this versatile IDE.