Ciberterminal Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== [DOC] Working with pluggable databases ======

====== Mangement ======
===== Create a pluggable database =====
<code sql>CREATE PLUGGABLE DATABASE PDB01 ;
</code>

===== Startup pluggable database =====
<code sql>
ALTER PLUGGABLE DATABASE PDB01 OPEN ;
</code>

===== tnsnames.ora =====
Tnsnames is necessary for connecting to the pluggable databases with cli tools (sql*, expdp...)
<code bash>
vim  $ORACLE_HOME/network/admin/tnsnames.ora
</code>
<code>
PDB01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB01)
    )
  )
</code>

====== Working with PDB's ======
===== List of Services (pdbs+cdbs) =====
<code sql>SELECT NAME, CON_ID FROM V$ACTIVE_SERVICES ;
</code>

===== Info about currently connected instance =====
<code sql>
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
 /
</code>
[[http://www.dba-oracle.com/t_pluggable_database.htm|link]]



===== List PDBS from current container =====
<code sql>
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;
</code>
[[http://www.dba-oracle.com/t_pluggable_database.htm|link]]

===== Connect from CDB =====
<code sql>
ALTER SESSION SET CONTAINER=PDB01 ;
</code>

===== With sql* =====
<code bash>
sqlplus 'sys/DBA@PDBTEST1 AS SYSDBA'
</code>







<code sql>
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
/

</code>
<code sql>

</code>
<code sql>

</code>





====== External doc ======
  * [[http://www.oracle-base.com/articles/12c/multitenant-connecting-to-cdb-and-pdb-12cr1.php]]