Oracle如何查看当前PDB容器信息

Oracle多租用户环境中，我们如何查看当前会话的容器信息呢？ 一般情况下，如果当前会话位于CDB$ROOT容器下，那么使用命令show pdbs可以查看所有的pdb数据库信息，如果当前会话信息位于某一个具体的PDB容器时，那么此时只能查看当前PDB的容器信息，具体如下所示：

sys@ctest> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PTESTUAT                       READ WRITE NO
         7 PDB3                           MOUNTED
sys@ctest> alter session set container=PDB1;

Session altered.

sys@ctest> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO
sys@ctest> alter session set container=CDB$ROOT;

Session altered.

sys@ctest> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PTESTUAT                       READ WRITE NO
         7 PDB3                           MOUNTED
sys@ctest>

有时候，我们使用的账号不是sysdba角色，此时使用show pdbs命令时，会由于权限不够而报错（SP2-0382: The SHOW PDBS command is not available）。 另外，有些工具，例如PL/SQL Developer中没法使用show pdbs这些命令，此时我们也可以使用下面SQL来查看当前PDB容器库信息以及会话信息。

sys@ctest> alter session set container=PDB2;

Session altered.

sys@ctest> set line 232
sys@ctest> col os_pid for a7
sys@ctest> col tracefile for a80 
sys@ctest> col username for a15
sys@ctest> col con_name for a10
sys@ctest> col schemaname for a10
sys@ctest> SELECT distinct s.con_id
  2       , c.con_name
  3       , s.username
  4       , s.user#
  5       , s.sid
  6       , s.serial#
  7       , s.prev_hash_value
  8       , schemaname
  9       , p.spid os_pid
 10  FROM V$SESSION S, v$process p, v$active_services c,
 11  (SELECT sid FROM v$mystat WHERE rownum=1) sid
 12   WHERE audsid = SYS_CONTEXT('userenv','sessionid')
 13   and p.addr = s.paddr
 14   and sid.sid = s.sid
 15   and s.username is not null
 16  and s.con_id=c.con_id
 17  and s.con_id=p.con_id
 18  /

    CON_ID CON_NAME   USERNAME             USER#        SID    SERIAL# PREV_HASH_VALUE SCHEMANAME OS_PID
---------- ---------- --------------- ---------- ---------- ---------- --------------- ---------- -------
         4 PDB2       SYS                      0        147      48906      1610794605 SYS        21336

1 row selected.

sys@ctest>