====== [DOC] Working with pluggable databases ======
====== Mangement ======
===== Create a pluggable database =====
CREATE PLUGGABLE DATABASE PDB01 ;
===== Startup pluggable database =====
ALTER PLUGGABLE DATABASE PDB01 OPEN ;
===== tnsnames.ora =====
Tnsnames is necessary for connecting to the pluggable databases with cli tools (sql*, expdp...)
vim $ORACLE_HOME/network/admin/tnsnames.ora
PDB01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB01)
)
)
====== Working with PDB's ======
===== List of Services (pdbs+cdbs) =====
SELECT NAME, CON_ID FROM V$ACTIVE_SERVICES ;
===== Info about currently connected instance =====
SET LINESIZE 200
SET PAGESIZE 10000
SET SERVEROUTPUT ON
COLUMN "DB DETAILS" FORMAT A100\
SELECT
'DB_NAME: ' ||sys_context('userenv', 'db_name')||
' / CDB?: ' ||(select cdb from v$database)||
' / AUTH_ID: ' ||sys_context('userenv', 'authenticated_identity')||
' / USER: ' ||sys_context('userenv', 'current_user')||
' / CONTAINER: '||nvl(sys_Context('userenv', 'con_Name'), 'NON-CDB')
"DB DETAILS"
FROM DUAL
/
[[http://www.dba-oracle.com/t_pluggable_database.htm|link]]
===== List PDBS from current container =====
COLUMN "RESTRICTED" FORMAT A10
select
v.name,
v.open_mode,
nvl(v.restricted, 'n/a') "RESTRICTED",
d.status
from
v$pdbs v
inner join
dba_pdbs d
using (GUID)
order by v.create_scn;
[[http://www.dba-oracle.com/t_pluggable_database.htm|link]]
===== Connect from CDB =====
ALTER SESSION SET CONTAINER=PDB01 ;
===== With sql* =====
sqlplus 'sys/DBA@PDBTEST1 AS SYSDBA'
SELECT
'DB_NAME: ' ||sys_context('userenv', 'db_name')||
' / CDB?: ' ||(select cdb from v$database)||
' / AUTH_ID: ' ||sys_context('userenv', 'authenticated_identity')||
' / USER: ' ||sys_context('userenv', 'current_user')||
' / CONTAINER: '||nvl(sys_Context('userenv', 'con_Name'), 'NON-CDB')
"DB DETAILS"
FROM DUAL
/
COLUMN "RESTRICTED" FORMAT A10
select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn
/
====== External doc ======
* [[http://www.oracle-base.com/articles/12c/multitenant-connecting-to-cdb-and-pdb-12cr1.php]]