Connecting to a Container Database (CDB)
Connecting to the root of a container database is the same as that of any previous database instance. On the database server you can use OS Authentication.
$ export ORACLE_SID=cdb1 $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 26 15:29:49 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL>
The
V$SERVICES views can be used to display available services from the database.COLUMN name FORMAT A30 SELECT name, pdb FROM v$services ORDER BY name; NAME PDB ------------------------------ ------------------------------ SYS$BACKGROUND CDB$ROOT SYS$USERS CDB$ROOT cdb1 CDB$ROOT cdb1XDB CDB$ROOT pdb1 PDB1 pdb2 PDB2 6 rows selected. SQL>
Displaying the Current Container
The
SHOW CON_NAME and SHOW CON_ID commands in SQL*Plus display the current container name and ID respectively.SQL> SHOW CON_NAME CON_NAME ------------------------------ CDB$ROOT SQL> SQL> SHOW CON_ID CON_ID ------------------------------ 1 SQL>
They can also be retrieved using the
SYS_CONTEXT function.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
SQL>
Switching Between Containers
When logged in to the CDB as an appropriately privileged user, the
ALTER SESSION command can be used to switch between containers within the container database.SQL> ALTER SESSION SET CONTAINER=pdb1; Session altered. SQL> SHOW CON_NAME CON_NAME ------------------------------ PDB1 SQL> ALTER SESSION SET CONTAINER=cdb$root; Session altered. SQL> SHOW CON_NAME CON_NAME ------------------------------ CDB$ROOT SQL>
From Oracle 12.2 onward the
ALTER SESSION command can also specify the service within the container. We can demonstrate this by creating a new service in a PDB, then switching to that service.CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1'
BEGIN
DBMS_SERVICE.create_service('my_new_service','my_new_service');
DBMS_SERVICE.start_service('my_new_service');
END;
/
ALTER SESSION SET CONTAINER=pdb1 SERVICE=my_new_service;
COLUMN con_id FORMAT A10
COLUMN service_name FORMAT A30
SELECT SYS_CONTEXT('USERENV', 'CON_ID') AS con_id,
SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service_name
FROM dual;
CON_ID SERVICE_NAME
---------- ------------------------------
6 my_new_service
SQL>
Connecting to a Pluggable Database (PDB)
Direct connections to pluggable databases must be made using a service. Each pluggable database automatically registers a service with the listener. This is how any application will connect to a pluggable database, as well as administrative connections.
SQL> -- EZCONNECT SQL> CONN system/password@//localhost:1521/pdb1 Connected. SQL> SQL> -- tnsnames.ora SQL> CONN system/password@pdb1 Connected. SQL>
The connection using a TNS alias requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file, such as the one shown below.
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-121.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)
PDB users with the SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a closed PDB. All other PDB users can only connect when the PDB is open. As with regular databases, the PDB users require the
CREATE SESSION privilege to enable connections.Data Pump Connections (expdp, impdp)
Connections to the
expdp and impdp utilities are unchanged, provided you specify a service.expdp username/password@service ... expdp \"username/password@service as sysdba\" ... impdp username/password@service ... impdp \"username/password@service as sysdba\" ...
Connections as SYSDBA must be to a common user. For example.
expdp scott/tiger@pdb1 tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log expdp \"sys/SysPassword1@pdb1 as sysdba\" tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log expdp \"c##myuser/MyPassword1@pdb1 as sysdba\" tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
