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 fWhich version of the database you are running?
Before that let’s understand the database release number format used by Oracle.
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.
Finding out the Oracle 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
SQL query to get database version
You can run on SQL* Plus or just copy query and run on SQL Developer.
Query 1:-
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
Query 2:-
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.
Use dbms_utility.Db_version package to check oracle version
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.
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.
I hope you found this small article helpful. Please share and subscribe.
Reference: