Oracle Container Database
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Data Dictionary Objects
CDB_FILE$ DBA_PDBS PDB_PLUG_IN_VIOLATION$
CDB_LOCAL_ADMINAUTH$ DBA_PDB_HISTORY PDB_PLUG_IN_VIOLATIONS VIEW
CDB_RESOURCE_PLAN$ DBMS_PDB PDB_SPFILE$
CDB_RESOURCE_PLAN_DIRECTIVE$ DBMS_PDB_EXEC_SQL V$CONTAINERS
CDB_SERVICE$ GV$CONTAINERS V$PDBS
CDB_HIST_PDB_INSTANCE GV$PDBS V$PDB_INCARNATION
CDB_PDBS GV_$PDB_INCARNATION WRI$_ADV_ADDM_PDBS
CDB_PDB_HISTORY PDB$SEED WRM$_PDB_INSTANCE
CDB$VIEW PDB_HISTORY$ XDB$CDBPORTS
DBA_HIST_PDB_INSTANCE    
 
Explore the Seed Pluggable Database
Create a pluggable database from the seed PDB$SEED CREATE PLUGGABLE DATABASE <pdb_name>
ADMIN USER <administrative_user> IDENTIFIED BY <password>
FILE_NAME_CONVERT = ('<pdbseed_path>', '<new_pdb_path>')
ROLES = (<comma_separated_role_list>)
DEFAULT TABLESPACE <tablespace_name> [DATAFILE <datafile_tempfile_spec>]
EXTENT MANAGEMENT LOCAL <AUTOALLOCATE | UNIFORM SIZE <extent_size>>
STORAGE <UNLIMITED | MAXSIZE | MAX_SHARED_TEMP_SIZE> <UNLIMITED | bytes>]
col con_name format a10
col restricted format a10

SELECT v.name, v.open_mode, v.restricted, d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v.create_scn;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        MOUNTED    NO         NORMAL

ALTER PLUGGABLE DATABASE pdborcl OPEN;

SELECT v.name, v.open_mode, v.restricted, d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v.create_scn;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        READ WRITE NO         NORMAL

SELECT * FROM dba_pdbs;

SELECT * FROM cdb$view(dba_pdbs);

SELECT * FROM cdb$view(cdb_pdbs);

SELECT table_name, tablespace_name, con_id
FROM dba_tables
WHERE rownum < 6;
SELECT table_name, tablespace_name, con_id
                                    *
ERROR at line 1:
ORA-00904: "CON_ID": invalid identifier


SELECT table_name, tablespace_name, con_id
FROM cdb$view(dba_tables)
WHERE rownum < 6;
 
Create a Pluggable Database From PDB$SEED
Add a pluggable database container CREATE PLUGGABLE DATABASE orabase
ADMIN USER uwclass IDENTIFIED BY uwclass
FILE_NAME_CONVERT = ('\pdbseed\', '\orabase\');
CREATE PLUGGABLE DATABASE orabase
                          *
ERROR at line 1:
ORA-44303: service name exists


-- in *NIX: use backslashes for Windows
CREATE PLUGGABLE DATABASE oradev
ADMIN USER uwclass IDENTIFIED BY uwclass
FILE_NAME_CONVERT = ('/pdbseed/', '/oradev/');

SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') RESTRICTED, d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v1;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
ORADEV                         MOUNTED    NO         NORMAL
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        READ WRITE n/a        NORMAL

ALTER SESSION SET CONTAINER=oradev;

SELECT name, created, open_mode
FROM v$database;

NAME        CREATED              OPEN_MODE
----------- -------------------- ---------
ORABASE     28-FEB-2013 15:40:05 MOUNTED

SELECT instance_name, database_status, active_state
FROM v$instance;

INSTANCE_NAME  DATABASE_STATUS  ACTIVE_STATUS
-------------- ---------------- --------------
orabase        ACTIVE           NORMAL

SELECT COUNT(*)
FROM dba_tables;
     *
ORA-01219: database or pluggable database no open: queries allowed on fixed tables or views only


sho con_name

CON_NAME
---------
ORADEV

ALTER PLUGGABLE DATABASE oradev OPEN;

SELECT COUNT(*)
FROM dba_tables;
Explore Data Files -- explore physical arch

desc dba_data_files

desc cdb_data_files

SELECT file_name, tablespace_name, online_status
FROM dba_data_files;

SELECT file_name, tablespace_name, online_status, con_id
FROM cdb_data_files;
Explore Users -- continuing in the PDBDEV container

desc dba_users

desc cdb_users

SELECT username, account_status, lock_date, expiry_date
FROM dba_users
ORDER BY 1;

SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
ORDER BY 1;

SELECT username, default_tablespace, temporary_tablespace, con_id
FROM cdb_users
ORDER BY 1;

SELECT username, default_tablespace, temporary_tablespace, con_id
FROM cdb_users
WHERE username like 'UW%'
ORDER BY 1;

ALTER PLUGGABLE DATABASE orabase OPEN;

SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v.create_scn;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        MOUNTED    n/a        NORMAL
ORADEV                         READ WRITE NO         NORMAL

SELECT username, default_tablespace, temporary_tablespace, con_id
FROM cdb_users
WHERE username like 'UW%'
ORDER BY 1;
Getting connected SQL> conn uwclass/uwclass@orabase
ERROR:
ORA-01045: user UWCLASS lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn sys@orabeta as sysdba
Enter password: *********
Connected.

SQL> GRANT create session TO uwclass;
GRANT create session TO uwclass
                        *
ERROR at line 1:
ORA-01917: user or role 'UWCLASS' does not exist


SQL> conn sys@orabase as sysdba
Enter password: *********
Connected.

SQL> GRANT create session TO uwclass;

Grant succeeded.

SQL> SELECT COUNT(*) FROM cdb_sys_privs;

COUNT(*)
----------
0

SQL> SELECT COUNT(*) FROM dba_sys_privs;

COUNT(*)
----------
987

SQL> SELECT privilege, admin_option, common
   2 FROM dba_sys_privs
   3 WHERE grantee = 'UWCLASS';

PRIVILEGE                                ADM COM
---------------------------------------- --- ---
CREATE SESSION                           NO  NO

col grantee format a30
col granted_role format a30

SELECT *
FROM dba_role_privs
WHERE grantee = 'UWCLASS';

GRANTEE                        GRANTED_ROLE                   ADM DEF COM
------------------------------ ------------------------------ --- --- ---
UWCLASS                        PDB_DBA                        YES YES NO
 
Create Pluggable Database From Clone
Create a test database in the same container as an existing test database CREATE PLUGGABLE DATABASE <new pdb> FROM <existing_pdb>
FILE_NAME_CONVERT=('<path>', '<path');
ALTER PLUGGABLE DATABASE orabase CLOSE;

ALTER PLUGGABLE DATABASE orabase OPEN READ ONLY;

CREATE OR REPLACE VIEW pdb_status AS
SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v.create_scn;

SELECT * FROM pdb_status;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        MOUNTED    n/a        NORMAL
ORABASE                        READ ONLY  NO         NORMAL

CREATE PLUGGABLE DATABASE orabase_t FROM orabase
FILE_NAME_CONVERT = ('\uwpdb\', '\uwpdbd\');

SELECT * FROM pdb_status;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        MOUNTED    n/a        NORMAL
ORABASE                        READ ONLY  NO         NORMAL
ORABASE_T                      MOUNTED    n/a        NEW
 
PDB Creation with UNPLUG/PLUG
Unplug Clause
Unplug Clause

Create from unplugged
ALTER PLUGGABLE DATABASE <pdb_name> UNPLUG INTO '<file_name>';
conn / as sysdba

-- get inside pdb

ALTER PLUGGABLE DATABASE oradev CLOSE;

ALTER PLUGGABLE DATABASE orabase UNPLUG INTO '/stage/oradev.xml/';

-- ftp files to new server

CREATE PLUGGABLE DATABASE orabase_new USING '/stage/oradev.xml'
NOCOPY TEMPFILE REUSE;

-- or

CREATE PLUGGABLE DATABASE orabase_new USING '/disk1/usr/oradev.xml'
SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/')
NOCOPY
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
TEMPFILE REUSE;

-- xml must reference the current location of the physical files are found

CREATE PLUGGABLE DATABASE orabase_new USING '/disk1/usr/oradev.xml'
COPY
PATH_PREFIX = '/disk2/oracle/sales' .. where the files are now
FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/'); -- converts the xml file from was to will be
 
ALTER PLUGGABLE DATABASE
Settings Clauses
Default Edition ALTER PLUGGABLE DATABASE <pdb_name>
conn / as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

CREATE EDITION demo_ed;

ALTER PLUGGABLE DATABASE pdbdev DEFAULT EDITION = demo_ed;
Default Tablespace ALTER PLUGGABLE DATABASE <pdb_name> DEFAULT TABLESPACE <tablespace_name>;
conn / as sysdba

ALTER SESSION SET CONTAINER = pdborcl;

SELECT tablespace_name
FROM dba_tablespaces;

ALTER PLUGGABLE DATABASE orabase DEFAULT TABLESPACE oradata;
Default Tablespace Type ALTER PLUGGABLE DATABASE <pdb_name> SET DEFAULT <BIGFILE | SMALLFILE> TABLESPACE;
conn / as sysdba

ALTER SESSION SET CONTAINER = pdborcl;

ALTER PLUGGABLE DATABASE orabase SET DEFAULT BIGFILE TABLESPACE;
Default Temporary Tablespace ALTER PLUGGABLE DATABASE <pdb_name> DEFAULT TEMPORARY TABLESPACE <tablespace_or_group>;;
conn / as sysdba

ALTER SESSION SET CONTAINER = pdborcl;

ALTER PLUGGABLE DATABASE orabase DEFAULT TEMPORARY TABLESPACEtemp_grp;
Rename ALTER PLUGGABLE DATABASE <pdb_name> RENAME GLOBAL_NAME TO <database>[.domain];
conn / as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

SELECT *
FROM global_name;

-- shutdown
-- reopen in restricted mode

ALTER PLUGGABLE DATABASE pdbdev RENAME GLOBAL_NAME TO pdb$dev;
Set Timezone only in a PDB

-- great except it doesn't work in Beta 2
ALTER PLUGGABLE DATABASE <pdb_name> SET TIME_ZONE = '<timezone_region>';
conn / as sysdba

ALTER SESSION SET CONTAINER = pdborcl;

SELECT systimestamp
FROM dual;

ALTER PLUGGABLE DATABASE pdborcl SET TIME_ZONE = 'Japan';

ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

ALTER SESSION SET CONTAINER = pdbdev;

ALTER PLUGGABLE DATABASE pdbdev SET TIME_ZONE = 'Japan';

SELECT systimestamp
FROM dual;
Set Timezone only in a PDB

-- great except it doesn't work in Beta 2
ALTER PLUGGABLE DATABASE <pdb_name> SET TIME_ZONE = '< | -> HH:MI';
conn / as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

SELECT systimestamp
FROM dual;

ALTER PLUGGABLE DATABASE
pdbdev SET TIME_ZONE = '01:00';

SELECT systimestamp
FROM dual;
Database Files  
 
Supplemental Logging  
 
Storage ALTER PLUGGABLE DATABASE <pdb_name> STORAGE UNLIMITED;
ALTER PLUGGABLE DATABASE pdborcl STORAGE UNLIMITED;
Storage ALTER PLUGGABLE DATABASE <pdb_name> STORAGE MAXSIZE <UNLIMITED | <size_clause>;
 
Storage ALTER PLUGGABLE DATABASE <pdb_name> STORAGE MAX_SHARED_TEMP_SIZE <UNLIMITED | <size_clause>;
 
Datafile Clause
Datafile Online / Offline ALTER PLUGGABLE DATABASE <pdb_name> DATAFILE ALL <ONLINE | OFFLINE>;
conn / as sysdba

ALTER SESSION SET CONTAINER = pdbdev;

ALTER PLUGGABLE DATABASE pdbdev CLOSE;

SELECT open_mode
FROM v$pdbs;

SELECT file#, status, enabled
FROM v$datafile;

ALTER PLUGGABLE DATABASE pdbdev DATAFILE ALL OFFLINE;
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


ALTER PLUGGABLE DATABASE pdbdev DATAFILE ALL ONLINE;

SELECT file#, status, enabled
FROM v$datafile;

ALTER PLUGGABLE DATABASE pdbdev OPEN;
Recovery Clauses
Backup and Recover a PDB ALTER PLUGGABLE DATABASE <pdb_name> RECOVER [AUTOMATIC] [FROM '<location>'  DATABASE;
-- there are also RECOVERY TABLESPACE, DATAFILE, LOGFILE, and CONTINUE variants.
ALTER PLUGGABLE DATABASE pdbdev RECOVER AUTOMATIC DATABASE;
Backup and Recover a PDB ALTER PLUGGABLE DATABASE <pdb_name> <BEGIN | END> BACKUP;
conn / as sysdba

ALTER PLUGGABLE DATABASE pdbdev BEGIN BACKUP;
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database


ALTER SESSION SET CONTAINER = PDBDEV;

ALTER PLUGGABLE DATABASE pdbdev BEGIN BACKUP;
*
ERROR at line 1:
ORA-01123: cannot start online backup; media recovery not enabled


ALTER PLUGGABLE DATABASE pdbdev END BACKUP;
*
ERROR at line 1:
ORA-01142: cannot end online backup - none of the files are in backup


-- enable recovery and repeat the above
Change State Clauses
Open Read Write ALTER PLUGGABLE DATABASE <pdb_name> OPEN READ [WRITE] [UPGRADE] [RESTRICTED] [FORCE]
[INSTANCES = <('<instance_name>' | <ALL [EXCEPT ('<instance_name'>)] >);
ALTER PLUGGABLE DATABASE pdborcl OPEN;
Open Read Only ALTER PLUGGABLE DATABASE <pdb_name> OPEN READ ONLY <instances_clause>;
ALTER PLUGGABLE DATABASE pdborcl OPEN READ ONLY;
Open Resetlogs ALTER PLUGGABLE DATABASE <pdb_name> OPEN RESETLOGS <instances_clause>;
ALTER PLUGGABLE DATABASE pdborcl OPEN RESETLOGS;
Open/Close a single PDB ALTER PLUGGABLE DATABASE <pdb_name> CLOSE [IMMEDIATE];
ALTER PLUGGABLE DATABASE orabase CLOSE;
Open/Close all PDBs ALTER PLUGGABLE DATABASE ALL CLOSE [IMMEDIATE];
ALTER PLUGGABLE DATABASE ALL CLOSE;
Open PDB in READ ONLY mode ALTER PLUGGABLE DATABASE <pdb_name> <CLOSE | OPEN> READ ONLY;
conn / as sysdba

-- must be closed
ALTER PLUGGABLE DATABASE orabase OPEN READ ONLY;
Open PDB in READ ONLY RESTRICTED mode ALTER PLUGGABLE DATABASE <pdb_name> OPEN READ ONLY RESTRICTED;
conn / as sysdba

ALTER PLUGGABLE DATABASE orabase CLOSE;

ALTER PLUGGABLE DATABASE orabase OPEN READ ONLY RESTRICTED;
Change State from Root Clauses
   
 
   
 
 
 
 
DROP PLUGGABLE DATABASE
Drop a pluggable database from its container DROP PLUGGABLE DATABASE <pdb_name> [INCLUDING DATAFILES];
ALTER PLUGGABLE DATABASE orabase CLOSE;

DROP PLUGGABLE DATABASE orabase INCLUDING DATAFILES;
 
Common Objects
This is just a placeholder for now as I can not get this to work  
/* Enable container_data sharing=object */
alter session set "_ORACLE_SCRIPT"=TRUE;

-- create an object sharing = LINKED

CREATE OR REPLACE VIEW morgan
CONTAINER_DATA SHARING=object
(DBID, INSTANCE_NUMBER, STARTUP_TIME, PARALLEL, VERSION,
DB_NAME, INSTANCE_NAME, HOST_NAME, LAST_ASH_SAMPLE_ID,
PLATFORM_NAME, CON_ID)
AS
SELECT dbid, instance_number, startup_time, parallel, version,
db_name, instance_name, host_name, last_ash_sample_id,
platform_name, con_dbid_to_id(dbid) con_id
FROM wrm$_database_instance;

-- create an object sharing = METADATA_LINK

CREATE OR REPLACE VIEW morgan
CONTAINER_DATA SHARING=metadata
(DBID, INSTANCE_NUMBER, STARTUP_TIME, PARALLEL, VERSION,
DB_NAME, INSTANCE_NAME, HOST_NAME, LAST_ASH_SAMPLE_ID,
PLATFORM_NAME, CON_ID)
AS
SELECT dbid, instance_number, startup_time, parallel, version,
db_name, instance_name, host_name, last_ash_sample_id,
platform_name, con_dbid_to_id(dbid) con_id
FROM wrm$_database_instance;

-- but so far I see no evidence of anything being shared.

CREATE OR REPLACE PACKAGE dbms_registry SHARING = NONE AUTHID DEFINER AS
CREATE OR REPLACE PACKAGE BODY dbms_registry SHARING = NONE AS
CREATE OR REPLACE DIRECTORY ctemp SHARING = NONE AS
CREATE TABLE xxx sharing = none (
 
Head
   

Related Topics
CDBVIEW
DBMS_PDB
DBMS_SQL
DBMS_SERVICE
DBMS_SYNC_REFRESH
Dynamic Performance Views
Editions
PDB$SEEDs
Tablespaces
Tablespace Groups

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved