Sometimes it’s the little things (Oracle Multitenant)

One of the simpler things that we tend to do in Oracle is run a query to determine which database we’re actually attached to. Maybe we want to display that on a screen or in a report, maybe we have logic to prevent something from running against production (or to only run it in production).

That query is typically either

select name from v$database;

or

select instance_name from v$instance;

HOWEVER. When you’re in a multitenant database, neither of those queries will return the value you expect. In both cases, they will return the name of the CDB (container database), and you’re probably going to be expecting the name of the PDB (pluggable database) instead.

There may be several ways to get this, but from the documentation I’ve seen, the preferred query is:

select sys_context('USERENV','CON_NAME') from dual;