Database Version
Sometimes, you would like to determine what release of Oracle database you are operating on in order to take some other action you need to perform, for example, downloading proper patches for your database.
There're 3 levels of version you should check:
Top-Level Version
Top-level version is the one you initially installed. To get current database version, we can query V$VERSION for sure.
Oracle Database 19c
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
As you can see, the base version is 19.0, although what I installed is 19.3 for Linux.
Please note that, if you didn't see Enterprise Edition in the result, it must be a Standard Edition. For databases in Standard Edition, some functions may need licenses to be used. For example, AWR report.
Oracle Database 11g
For Oracle database version 11g, it's a little longer.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Full Installer vs Upgraded
For Oracle 11g, almost every release has its own full installer pack, for instances, 11.2.0.1, 11.2.0.2, 11.2.0.4. Therefore, release 11.2.0.4 does not necessarily upgrade from 11.2.0.2 or 11.2.0.1. Please check Oracle eDelivery for more software variations.
On the contrary, for Oracle 19c, almost every minor release is updated (patched) from the base. Even though 19.3 package for download looks like a full installer, it's also a base plus release update one. This is a very good scheme design.
Software Patched Level
On the software-side, its patch history can be found by OPatch utility. Here we issue some opatch commands.
opatch lspatches
[oracle@test ~]$ $ORACLE_HOME/OPatch/opatch lspatches
31771877;Database Release Update : 19.9.0.0.201020 (31771877)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
opatch lsinventory
[oracle@test ~]$ $ORACLE_HOME/OPatch/opatch lsinventory | grep "Patch description"
Patch description: "Database Release Update : 19.9.0.0.201020 (31771877)"
Patch description: "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
As you can see, the database software is patched up to 19.9.0.0.201020, whereas Oracle ClusterWare (OCW) is not updated because the database is a single-instance one.
SQL Patched Level
On the data-side, we can query DBA_REGISTRY_SQLPATCH for sure.
SQL> select description from dba_registry_sqlpatch order by action_time desc;
DESCRIPTION
--------------------------------------------------------------------------------
Database Release Update : 19.9.0.0.201020 (31771877)
Database Release Update : 19.3.0.0.190416 (29517242)
As you can see, the SQL patch version is updated to 19.9.0.0.201020, it's the same as the Oracle Database's.