[DOC] Working with pluggable databases

Mangement

CREATE PLUGGABLE DATABASE PDB01 ;
ALTER PLUGGABLE DATABASE PDB01 OPEN ;

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

SELECT NAME, CON_ID FROM V$ACTIVE_SERVICES ;
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
 /

link

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;

link

ALTER SESSION SET CONTAINER=PDB01 ;
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

  • oracle/12c_pluggable.txt
  • Last modified: 2018/04/27 10:58
  • by dodger