Basic admin for Oracle 12c MultiTenant

Connecting to PDBs

SELECT name, pdb FROM   v$services ORDER BY name;

Display current container

SQL> SHOW CON_NAME

Switching Between Containers

SQL> ALTER SESSION SET CONTAINER=pdb1;

SQL> ALTER SESSION SET CONTAINER=cdb$root;

Startup/shutdown

SELECT name, open_mode FROM v$pdbs;

ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;

Preserve PDB Startup State

ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

Managing Users

Multitenant environments there are two types of user.

• Common User : The user is present in all containers (root and all PDBs).
• Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.

Create common users
CONN / AS SYSDBA

-- Create the common user using the CONTAINER clause.
CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;


Create local users
CONN / AS SYSDBA

-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdb1;

-- Create the local user using the CONTAINER clause.
CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;

Tracking PDBs to Datafiles

Map tables to PDBs:

Checking PDB History