Database Reference
In-Depth Information
Oracle has once again increased the number of keys available in the USERENV realm to allow you to query
which container you are connected to. This can be quite useful in maintenance scripts to work out that you are not
accidentally connected to the wrong PDB! You can query the container number as well as the container name you are
connected to as shown in this example:
SQL> select sys_context('userenv','con_name') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
SQL> select sys_context('userenv','con_id') from dual
SYS_CONTEXT('USERENV','CON_ID')
--------------------------------------------------------------------------------
1
If you were a bit lazy, then you could write a special piece of code in your login.sql to show the container you are
connected to:
col usr new_value usr
col con new_value con
define con=started
define usr=not
set termout off
select sys_context('userenv','con_name') as con, user usr from dual;
set termout on
set sqlprompt '&&usr.@&&con.> '
The root container has a new class of views, called CDB-views. These contain information about the root and
all its PDBs. This is useful to get a full overview of the database, but it will not list information for PDBs which are
mounted and not open. Additionally, the user executing the query needs to have the privilege to view the PDB.
Consider the following example from the Container Database CDB1, executed as SYS in CDB$ROOT:
SQL> select con_id,open_mode, name from v$pdbs;
CON_ID OPEN_MODE NAME
---------- ---------- ------------------------------
2 READ ONLY PDB$SEED
3 MOUNTED SWINGBENCH1
4 MOUNTED SWINGBENCH2
As you can see the only user-PDB, swingbench1, is mounted (closed). A PDB can either be mounted or open.
Querying the container data object CDB_DATA_FILES will not show data files belonging to the PDB:
SQL> select count(*) from cdb_data_files where con_id=3;
COUNT(*)
----------
0
Search WWH ::
Custom Search