How to Check Oracle Version | A Detailed Guide

Keeping track of the version of your Oracle Database is crucial for ensuring compatibility with applications, receiving the latest updates, and maintaining a secure environment. Oracle Corporation releases periodic updates and patches, making it essential to know which version you are running. 

There are several methods to determine the version of your Oracle Database. These methods can be executed using SQL*Plus, Oracle SQL Developer, or other SQL query tools. This article provides a comprehensive guide on how to check the version of your Oracle Database.

Why Checking Your Oracle Database Version is Important

1. Compatibility: Ensuring your applications are compatible with the database version.
2. Support: Verifying if your version is still supported by Oracle.
3. Features: Taking advantage of new features and improvements.
4. Security: Applying the latest security patches and updates.

What Is My Oracle Version?

To check the version of your Oracle Database, you have several methods at your disposal. You can use SQL*Plus, Oracle Enterprise Manager, or even command-line tools. Here’s a detailed guide on how to do it, ensuring you have all the information you need.

Query to Check Oracle Version in SQL Developer

1. Connect to the Database: Open your SQL*Plus command line or Oracle SQL Developer and connect to your database using the appropriate credentials.
2. Execute the SQL Query: Run the following SQL query to get the version information:

SELECT * FROM v$version;

This query will return multiple rows with information about the Oracle Database, PL/SQL, and other components. Look for the row that mentions “Oracle Database” to find your version. Here is an example output:

BANNER

—————————————————————-

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

PL/SQL Release 19.0.0.0.0 – Production

CORE    19.0.0.0.0      Production

TNS for Linux: Version 19.0.0.0.0 – Production

NLSRTL Version 19.0.0.0.0 – Production

Check Oracle Version Using the Data Dictionary View

1. Connect to the Database: As with the previous method, connect to your database using SQL*Plus or Oracle SQL Developer.
2. Execute the SQL Query: Run the following SQL query:

SELECT version FROM product_component_version WHERE product LIKE ‘Oracle%’;

This will provide a concise version number of the Oracle Database. Here is an example output:

VERSION

—————————————————————-

19.0.0.0.0

Check Oracle Version Using Oracle Enterprise Manager

If you have Oracle Enterprise Manager (OEM) installed, you can check the version through its graphical interface.

1. Log in to OEM: Open your web browser and log in to Oracle Enterprise Manager.
2. Navigate to Database: Go to the “Targets” tab and select “Databases.”
3. Select Your Database: Click on the name of your database.
4. View Version Information: The database home page will display the version information, typically under the “General” section.

How to Check Oracle Version in cmd?

If you prefer using the command line, you can check the Oracle version with the following steps:

1. Set the Oracle Environment: Ensure your environment variables are set correctly. For example, on Unix-based systems:

. oraenv

2. Run the sqlplus Command:

sqlplus -v

This command outputs the SQL*Plus version, which includes the Oracle Database version it is connected to.

Frequently Asked Questions

Can I check the Oracle version without logging in?

You need to log in to the database or have access to the system where Oracle is installed to check the version.

What if I only have the Oracle client installed?

If you only have the client installed, you can use the sqlplus -v command to check the client version, which may not necessarily match the server version.

Conclusion

Whether using SQL*Plus, Oracle SQL Developer, or Oracle Enterprise Manager, the steps to check the version are straightforward and should be a regular part of your database maintenance routine. Stay informed about your Oracle version to ensure optimal performance and security for your applications and data.