Oracle offers several methods for finding the name of a database.
More significantly, 12c introduces new functionality which may change the expected value from some of the old methods due to the multi-tenant feature.
Here are 11 methods for finding the name of a database.
SELECT 'ora_database_name' method, ora_database_name VALUE FROM DUAL UNION ALL SELECT 'SYS_CONTEXT(userenv,db_name)', SYS_CONTEXT('userenv', 'db_name') FROM DUAL UNION ALL SELECT 'SYS_CONTEXT(userenv,db_unique_name)', SYS_CONTEXT('userenv', 'db_unique_name') FROM DUAL UNION ALL SELECT 'SYS_CONTEXT(userenv,con_name)', SYS_CONTEXT('userenv', 'con_name') FROM DUAL UNION ALL SELECT 'SYS_CONTEXT(userenv,cdb_name)', SYS_CONTEXT('userenv', 'cdb_name') FROM DUAL UNION ALL SELECT 'V$DATABASE name', name FROM v$database UNION ALL SELECT 'V$PARAMETER db_name', VALUE FROM v$parameter WHERE name = 'db_name' UNION ALL SELECT 'V$PARAMETER db_unique_name', VALUE FROM v$parameter WHERE name = 'db_unique_name' UNION ALL SELECT 'GLOBAL_NAME global_name', global_name FROM global_name UNION ALL SELECT 'DATABASE_PROPERTIES GLOBAL_DB_NAME', property_value FROM database_properties WHERE property_name = 'GLOBAL_DB_NAME' UNION ALL SELECT 'DBMS_STANDARD.database_name', DBMS_STANDARD.database_name FROM DUAL;
The results of these will vary by version, whether the db is a container or not, and if its is a container, whether the query runs within a pluggable database or the container root database.
Note, the con_name and cdb_name options for the SYS_CONTEXT function do not exist in 11g or lower. So those queries in the union must be removed to execute in an 11g database. Within a pluggable database some of the methods recognize the PDB as the database, while others recognize the container as the database. Also note in 12cR1 there is a bug where SYS_CONTEXT(‘userenv’,’db_name’) will return the container name for a PDB instead of the PDB itself. This is fixed in 12cR2 and above, it can also be fixed with patch 22743059 from MOS.
So, if you are using any of these methods in an 11g database and you upgrade to a 12c pluggable db, you may expect the PDB name to be returned, but instead you’ll get the CDB name instead.
Also note, some of the methods always return the name in capital letters, others will return the exact value used to create the database.
METHOD | 12c Non-CDB |
12c CDB$Root |
12cR1 PDB |
12cR2+ PDB |
11g |
GLOBAL_NAME global_name | SDS12CR1 | SDSCDB1 | SDSPDB1 | SDSPDB2 | SDS11GR2 |
DATABASE_PROPERTIES GLOBAL_DB_NAME | SDS12CR1 | SDSCDB1 | SDSPDB1 | SDSPDB2 | SDS11GR2 |
DBMS_STANDARD.database_name | SDS12CR1 | SDSCDB1 | SDSPDB1 | SDSPDB2 | SDS11GR2 |
ora_database_name | SDS12CR1 | SDSCDB1 | SDSPDB1 | SDSPDB2 | SDS11GR2 |
V$DATABASE name | SDS12CR1 | SDSCDB1 | SDSCDB1 | SDSCDB2 | SDS11GR2 |
SYS_CONTEXT(userenv,db_name) | sds12cr1 | sdscdb1 | sdscdb1 | sdspdb2 | sds11gr2 |
SYS_CONTEXT(userenv,db_unique_name) | sds12cr1 | sdscdb1 | sdscdb2 | sds11gr2 | |
SYS_CONTEXT(userenv,con_name) | sds12cr1 | CDB$ROOT | SDSPDB1 | SDSPDB2 | n/a |
SYS_CONTEXT(userenv,cdb_name) | sdscdb1 | sdscdb1 | sdscdb2 | n/a | |
V$PARAMETER db_name | sds12cr1 | sdscdb1 | sdscdb1 | sdscdb2 | sds11gr2 |
V$PARAMETER db_unique_name | sds12cr1 | sdscdb1 | sdscdb1 | sdscdb2 | sds11gr2 |
On a related note, only the container of a multi-tenant database has instances. So, while PDBs can declare their own name for the database level with some methods above; there is no corresponding PDB-instance name functionality.
Good explanation! Thanks