In Oracle 12c, you can connect to a PDB using two methods :
– Switch the container using Alter system set container …
– Use connect command to connect to PDB using network alias
Let’s compare the two methods :
The use of SET CONTAINER avoids the need to create a new connection from scratch.
If there is an existing connection to a PDB / CDB$root, the same connection can be used to connect to desired PDB / CDB$root.
– Connect to CDB
[oracle@em12 ~]$ sqlplus system/oracle@cdb1 CDB$ROOT@CDB1> sho con_name CON_NAME ------------------------------ CDB$ROOT
– Check the PID for the process created on the operating system
[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep
oracle 23271 1 0 10:23 ? 00:00:00 oraclecdb1 (LOCAL=NO)
– Change the container to PDB1 using Set container
CDB$ROOT@CDB1> alter session set container=pdb1; sho con_name CON_NAME ------------------------------ PDB1
– Check that the operating system PID remains the same as earlier connection is reused and a new connection has not been created
[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep
oracle 23271 1 0 10:23 ? 00:00:00 oraclecdb1 (LOCAL=NO)
– Switch the container back to cdb$root using connect
CDB$ROOT@CDB1> conn system/oracle@cdb1 sho con_name CON_NAME ------------------------------ CDB$ROOT
– Check that a new operating system PID has been created as a new connection has been created
[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep
oracle 23409 1 0 10:29 ? 00:00:00 oraclecdb1 (LOCAL=NO)
glogin.sql is not executed when Alter session set container is used
To demonstrate it, I have added following lines to my glogin.sql to display CDB/PDB name in SQL prompt:
define gname=idle
column global_name new_value gname
set heading off
set termout off
col global_name noprint
select upper(sys_context ('userenv', 'con_name') || '@' || sys_context('userenv', 'db_name')) global_name from dual;
set sqlprompt '&gname> '
set heading on
set termout on
- Let’s connect to PDB1 using “Connect” and verify that glogin.sql is executed and prompt displays CDB/PDB name
SQL> conn sys/oracle@pdb1 as sysdba
PDB1@CDB1>
- Verify that the prompt displays current container (PDB1) and container database (CDB1)
PDB1@CDB1> sho con_name PDB1 PDB1@CDB1> sho parameter db_name db_name string cdb1
– Now let’s connect to PDB2 using Alter session set container and verify that glogin.sql is not executed and the same prompt as earlier is displayed
PDB1@CDB1> alter session set container=pdb2; Session altered.
PDB1@CDB1> sho con_name
CON_NAME
------------------------------
PDB2
-- Let's connect to PDB2 using connect and verify that glogin.sql is executed as the prompt displays the PDB name PDB2 PDB1@CDB1> connect sys/oracle@pdb2 as sysdba PDB2@CDB1>
Pending transactions are not committed when Alter system set container is used
– Let’s start a transaction in PDB1
PDB1@CDB1> create table pdb1_tab(x number);
Table created.
PDB1@CDB1> insert into pdb1_tab values (1);
1 row created.
– Switch the container to PDB2
PDB1@CDB1> alter session set container=pdb2;
– Try to start another transaction on PDB2 – does not allow as an active transaction exists in the parent container PDB1
PDB1@CDB1> create table pdb2_tab (x number); create table pdb2_tab (x number) * ERROR at line 1: ORA-65023: active transaction exists in container PDB1
– In another session check that the transaction was not committed and no rows are visible in table pdb1_tab
CDB$ROOT@CDB1> conn system/oracle@pdb1 PDB1@CDB1> select * from pdb1_tab;
no rows selected
Alter session set container cannot be used by local users
– Try to give set container privilege to a local user HR in PDB2 – fails as common privilege cannot be granted to a local user and hence a local user cannot user alter session set container to connect to another PDB
PDB2@CDB1> connect system/oracle@pdb2
PDB2@CDB1> grant set container to hr container=all;
grant set container to hr container=all
*
ERROR at line 1:
ORA-65030: one may not grant a Common Privilege to a Local User or Role
I hope this post was useful.
Your comments and suggestions are always welcome.
References :
http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#ADMIN13970
Related Links:
12c : Connecting To PDB’s With Same Name
12c: USE_SID_AS_SERVICE_listener_name
——————————————————–