Oracle Multitenant - PDB New Clauses - Containers (12.1.0.2) - Part I

As you know, Oracle Database 12c Release 1 (12.1.0.2.0) already released at July 22, 2014.
You can download new release from here.

Oracle Multitenant option also supports some news in Oracle Database 12c Release 1 (12.1.0.2.0).
With new release coming new PDB Clauses.

This article series covers following new clauses:

1. PDB Containers Clause
2. PDB Standbys Clause
3. PDB Logging Clause  (as soon)

Part I: PDB Containers Clause

The CONTAINERS clause in PDB enables you to query user-created tables and views across all PDBs in a CDB.The tables and views, or synonyms of them, specified in the CONTAINERS clause must exist in the root and in all PDBs and must be owned by the common user. A query that includes the CONTAINERS clause must be run in the root.

Follow examples:

[oracle@oel62-ora12c2 ~]$ export ORACLE_SID=prmcdb
[oracle@oel62-ora12c2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 24 16:02:21 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select name, open_mode from v$database;

NAME   OPEN_MODE
--------- --------------------
PRMCDB   READ WRITE

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         READ WRITE
PRMPDB02         READ WRITE
PRMPDB03         READ WRITE
PRMPDB04         READ WRITE

SQL> create user c##mahir identified by mahir;

User created.

SQL> grant create session, resource, dba to c##mahir container=ALL;

Grant succeeded.

SQL> 
SQL> conn c##mahir@prmpdb01
Enter password: 
Connected.
SQL> 
SQL> create table t as select 1 as n from dual;

Table created.

SQL> select n from  t;

  N
----------
  1

SQL> conn c##mahir/mahir@prmpdb02
Connected.
SQL> create table t as select 2 as n from dual;

Table created.

SQL> select n from t;

  N
----------
  2

SQL> conn c##mahir/mahir@prmpdb03 
Connected.
SQL> create table t as select 3 as n from dual;

Table created.

SQL> select n from t;

  N
----------
  3

SQL> conn c##mahir/mahir@prmpdb04
Connected.
SQL> create table t as select 4 as n from dual;

Table created.

SQL> select n from t;

  N
----------
  4

SQL> conn c##mahir/mahir@prmcdb
Connected.
SQL> create table t (n number);

Table created.

SQL> 
SQL> select *  from containers(t);

  N     CON_ID
---------- ----------
  3     5
  4     6
  1     3
  2     4

SQL> select con_id, n from containers(t) order by 1;

    CON_ID     N
---------- ----------
  3     1
  4     2
  5     3
  6     4
  
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database prmpdb01 close;

Pluggable database altered.

SQL> 
SQL> conn c##mahir/mahir@prmcdb
Connected.
SQL> select con_id, n from containers(t) order by 1;

    CON_ID     N
---------- ----------
  4     2
  5     3
  6     4
  
SQL> select con_id , n from containers (t) where con_id in (4,5);

    CON_ID     N
---------- ----------
  5     3
  4     2

If tables created with local users, how we can to query user-created tables across all PDBs in a CDB?
We must create a view in all PDBs and in CDB with common user like as below:

[oracle@oel62-ora12c2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 24 16:55:10 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn mahir/mahir@prmpdb01;
Connected.
SQL> select * from dt;

D
---------
23-JUL-14

SQL> conn mahir/mahir@prmpdb02;
Connected.
SQL> select *  from dt;

D
---------
20-JUL-14

SQL> conn mahir/mahir@prmpdb03;
Connected.
SQL> select * from dt;

D
---------
22-JUL-14

SQL> conn mahir/mahir@prmpdb04;
Connected.
SQL> select * from dt;

D
---------
21-JUL-14

As you seen, dt table owned by local user mahir.
Create views in all PDBs and CDB with common user c##mahir.

[oracle@oel62-ora12c2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 24 17:04:52 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> grant select any table to c##mahir container=ALL;

Grant succeeded.

SQL> conn c##mahir/mahir@prmpdb01
Connected.
SQL> create view vw_dt as select *  from mahir.dt;

View created.

SQL> conn c##mahir/mahir@prmpdb02
Connected.
SQL> create view vw_dt as select *  from mahir.dt;

View created.

SQL> conn c##mahir/mahir@prmpdb03
Connected.
SQL> create view vw_dt as select *  from mahir.dt;

View created.

SQL> conn c##mahir/mahir@prmpdb04
Connected.
SQL> create view vw_dt as select *  from mahir.dt;

View created.

All views created in all PDBs with common user c##mahir.
Create empty dt table and vw_dt view in root with common user c##mahir.

SQL> conn c##mahir/mahir@prmcdb
Connected.
SQL> create table dt (d date);

Table created.

SQL> create view vw_dt as select *  from dt;

View created.

SQL> select *  from containers(vw_dt);

D       CON_ID
--------- ----------
23-JUL-14    3
21-JUL-14    6
22-JUL-14    5
20-JUL-14    4

As you seen, we select on containers(vw_dt) returns all rows from across PDBs.

Regards
Mahir M. Quluzade