Oracle is a relational database management system by Oracle Corporation. It is one of the most widely used databases in OLTP, Data warehouse. Oracle released a good number of versions since 1979 and the latest version is 19C which is available for on-premise as well as in the cloud.
Many of the users are still using 11g, 12c, and 18C. So Let’s find out which version of the database you are running?
Find Oracle Database Version
Oracle maintains version information in V$VERSION, PRODUCT_COMPONENT_VERSION data dictionary Views, or Metadata Table. Just run a query against these views to find out the database version.
- V$VERSION – Version numbers of core library components in the Oracle Database.
- PRODUCT_COMPONENT_VERSION – Version and status information for component products
1. Using V$VERSION View
You can run on SQL* Plus or just copy query and run on SQL Developer.
COL BANNNER FORMAT A200 COL CON_ID FORMAT 99 SELECT * FROM v$version; BANNER CON_ID -------------------------------------------------------------------------------- ------ Oracle Database 10g Enterprise Edition Release 10.1.0.1.0 - 64bit Production 0 PL/SQL Release 10.1.0.1.0 - Production 0 CORE 10.1.0.1.0 Production 0 TNS for Linux: Version 10.1.0.1.0 - Production 0 NLSRTL Version 10.1.0.1.0 - Production 0
2. Using PRODUCT_COMPONENT_VERSION View
COL PRODUCT FORMAT A50 COL VERSION FORMAT A20 COL STATUS FORMAT A40 SELECT * FROM product_component_version; PRODUCT VERSION STATUS -------------------------------------------------- -------------------- ---------------------------- NLSRTL 10.1.0.1.0 Production Oracle Database 10g Enterprise Edition 10.1.0.1.0 64bit Production PL/SQL 10.1.0.1.0 Production TNS for Linux: 10.1.0.1.0 Production
The output of the above query is 10.1.0.1.0. That means you are running 10G Version.
3. Using DBMS_UTILITY.DB_VERSION Package
Another method is using dbms_utility.Db_version package available in the database. It is PL/SQL package, so you need to run the below script to get version information.
dbms_utility.db_version returns version and compatibility information.
PL/SQL Script to get DB Version in Oracle,
DECLARE
v1 VARCHAR2(100);
v2 VARCHAR2(100);
BEGIN
dbms_utility.Db_version (v1, v2);
dbms_output.Put_line('Version ' || v1);
dbms_output.Put_line('Compatability ' || v2);
END;
Output
Version 19.0.0.0.0 Compatability 19.0.0
It is 19C.
Let’s understand Oracle Database Release Number Format
Internally, Oracle maintains versions as five numeric segments (XX.X.X.X.X) divided by periods. e.g for 10G version could be 10.1.0.1.0 or10.1.0.1.1.
Meaning of each segment,
- First numeral – Major database release number e.g 12c, 18c.
- Second numeral – Database maintenance release number.
- Third numeral – Application server release number.
- Fourth numeral – Component-specific release number.
- Fifth numeral – Platform-specific release number.
Summary
Knowing the version information is useful when you open a support request or when a patch needs to be applied. It also helps developers to understand the features available in the release.
Reference: – Oracle 11G Database Administrator’s Guide
Quick Recap:
- Oracle follows a five-segment versioning system.
- You can check the version using:
- V$VERSION view (SQL Query)
- PRODUCT_COMPONENT_VERSION view (SQL Query)
- DBMS_UTILITY.DB_VERSION (PL/SQL Script)
Next Steps:
If you’re running an older version, consider upgrading to Oracle 19C for improved performance and security.
Need help with Oracle databases? Comment below with your questions!
🚀 Found this guide helpful? Share it with others and subscribe for more Oracle tips!