How get current container DBID using sys_context ('userenv'?

From: Luis Claudio Dias dos Santos <lsantos_at_pobox.com>
Date: Mon, 30 Sep 2019 14:13:26 -0300
Message-ID: <CAPWdmV-=phcob+Wyvhy1BAZqv-cTKFNxAP33jfn30MYGfe2rDw_at_mail.gmail.com>

Hi

I have a CDB with two PDBS.

U71013576_at_P11CDB6.ecc03cas06vm01 [18c RAC] select sys_context
('userenv','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')

---

CDB$ROOT U71013576_at_P11CDB6.ecc03cas06vm01 [18c RAC] _at_pdbs

INST_ID PDB_ID PDB_NAME                               DBID STATUS

LOGGING CREATION_TIME CREATION_SCN OPEN_TIME  OPEN_MODE

------- ------ -------------------- ---------------------- ----------
--------- ------------- --------------- ------------------------------
----------
      6      2 PDB$SEED                         1805877491 NORMAL
LOGGING   05/03/18              1477953 18/09/19 15:50:29,349 -03:00   READ
ONLY
             3 P02NGP                            548521466 NORMAL
LOGGING   16/09/19       15784573530790
 MOUNTED
             4 P00NGP                            630270457 NORMAL
LOGGING   17/09/19       15784573918734 18/09/19 16:33:14,867 -03:00   READ

WRITE

      7      2 PDB$SEED                         1805877491 NORMAL
LOGGING   05/03/18              1477953 18/09/19 15:50:29,478 -03:00   READ
ONLY
             3 P02NGP                            548521466 NORMAL
LOGGING   16/09/19       15784573530790
 MOUNTED
             4 P00NGP                            630270457 NORMAL
LOGGING   17/09/19       15784573918734 18/09/19 16:33:14,873 -03:00   READ

WRITE

      8      2 PDB$SEED                         1805877491 NORMAL
LOGGING   05/03/18              1477953 18/09/19 15:50:28,657 -03:00   READ
ONLY
             3 P02NGP                            548521466 NORMAL
LOGGING   16/09/19       15784573530790 18/09/19 16:08:25,995 -03:00
MOUNTED
             4 P00NGP                            630270457 NORMAL
LOGGING   17/09/19       15784573918734 18/09/19 16:33:14,871 -03:00   READ

WRITE When I check for DBID using userenv I got the CDB$ROOT DBID. That's OK, while this is the current container.

U71013576_at_P11CDB6.ecc03cas06vm01 [18c RAC] r   1 select

  2   sys_context ('userenv','DBID') DBID,
  3   sys_context ('userenv','DB_NAME') DB_NAME,
  4   sys_context ('userenv','DB_UNIQUE_NAME') DB_UNIQUE_NAME

  5* from dual

DBID                           DB_NAME

 DB_UNIQUE_NAME

------------------------------ ----------------------------------------
----------------------------------------
2416754358                     P11CDB

P11CDB But when I "move" to the PDB...

U71013576_at_P11CDB6.ecc03cas06vm01 [18c RAC] alter session set container=P00NGP;

U71013576_at_P11CDB6.ecc03cas06vm01 [18c RAC] select sys_context
('userenv','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')

---

P00NGP U71013576_at_P11CDB6.ecc03cas06vm01 [18c RAC] _at_pdbs

INST_ID PDB_ID PDB_NAME                               DBID STATUS

LOGGING CREATION_TIME CREATION_SCN OPEN_TIME  OPEN_MODE

------- ------ -------------------- ---------------------- ----------
--------- ------------- --------------- ------------------------------
----------
      6      4 P00NGP                            630270457 NORMAL
LOGGING   17/09/19       15784573918734 18/09/19 16:33:14,867 -03:00   READ
WRITE
      7      4 P00NGP                            630270457 NORMAL
LOGGING   17/09/19       15784573918734 18/09/19 16:33:14,873 -03:00   READ
WRITE
      8      4 P00NGP                            630270457 NORMAL
LOGGING   17/09/19       15784573918734 18/09/19 16:33:14,871 -03:00   READ

WRITE U71013576_at_P11CDB6.ecc03cas06vm01 [18c RAC] r   1 select

  2  sys_context ('userenv','DBID') DBID,
  3  sys_context ('userenv','DB_NAME') DB_NAME,
  4  sys_context ('userenv','DB_UNIQUE_NAME') DB_UNIQUE_NAME

  5* from dual

DBID                           DB_NAME

 DB_UNIQUE_NAME

------------------------------ ----------------------------------------
----------------------------------------
2416754358                     P00NGP

P11CDB The DBID is still the CDB$ROOT DBID, *2416754358*, and not the current PDB DBID, *630270457*. In other words: ('userenv','DBID') is not following ('userenv','DB_NAME'), but ('userenv','DB_UNIQUE_NAME').

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 30 2019 - 19:13:26 CEST