12.1.0.2中引入了PDB CONTAINERS，有了她之后，可以跨PDB查询同一用户下的同一张表或视图。当然，这也是为了方便Oracle自身，其实CDB_开头的视图采用的也是CONTAINERS字句。不知道在12.1.0.1上是什么样子，感兴趣的可以看看。
官方文档的介绍The CONTAINERS clause is a new way of looking at multitenant container databases (CDBs). With this clause, data can be aggregated from a single identical table or view across many pluggable databases (PDBs) from the root container. The CONTAINERS clause accepts a table or view name as an input parameter that is expected to exist in all PDBs in that container. Data from a single PDB or a set of PDBs can be included with the use of CON_ID in the WHERE clause. For example:
SELECT ename FROM CONTAINERS(scott.emp) WHERE CON_ID IN (45, 49);
Containers功能主要还是为Oracle自身服务
SQL> col owner for a10
SQL> col view_name for a20
SQL> set lines 156 pages 156
SQL> select owner,view_name,con_id from cdb_views where view_name='CDB_DATA_FILES';
OWNER VIEW_NAME CON_ID ---------- -------------------- ---------- SYS CDB_DATA_FILES 4 SYS CDB_DATA_FILES 3 SYS CDB_DATA_FILES 1SQL> select text_vc from cdb_views where view_name='CDB_DATA_FILES';
TEXT_VC
-------------------------------------------------------------------
SELECT "FILE_NAME","FILE_ID","TABLESPACE_NAME","BYTES","BLOCKS","STATUS","RELATIVE_FNO","AUTOEXTENSIBLE","MAXBYTES","MAXBLOCKS","INCREMENT_BY","USER_BYTES",
"USER_BLOCKS","ONLINE_STATUS","CON_ID" FROM CONTAINERS("SYS"."DBA_DATA_FILES")
SELECT "FILE_NAME","FILE_ID","TABLESPACE_NAME","BYTES","BLOCKS","STATUS","RELATIVE_FNO","AUTOEXTENSIBLE","MAXBYTES","MAXBLOCKS","INCREMENT_BY","USER_BYTES",
"USER_BLOCKS","ONLINE_STATUS","CON_ID" FROM CONTAINERS("SYS"."DBA_DATA_FILES")
SELECT "FILE_NAME","FILE_ID","TABLESPACE_NAME","BYTES","BLOCKS","STATUS","RELATIVE_FNO","AUTOEXTENSIBLE","MAXBYTES","MAXBLOCKS","INCREMENT_BY","USER_BYTES",
"USER_BLOCKS","ONLINE_STATUS","CON_ID" FROM CONTAINERS("SYS"."DBA_DATA_FILES")
SQL>SQL> alter session set container=pdb11;
Session altered.
SQL> select text_vc from cdb_views where view_name='CDB_DATA_FILES';
TEXT_VC
-----------------------------------------------------------------------
SELECT "FILE_NAME","FILE_ID","TABLESPACE_NAME","BYTES","BLOCKS","STATUS","RELATIVE_FNO","AUTOEXTENSIBLE","MAXBYTES","MAXBLOCKS","INCREMENT_BY","USER_BYTES",
"USER_BLOCKS","ONLINE_STATUS","CON_ID" FROM CONTAINERS("SYS"."DBA_DATA_FILES")
SQL>SQL> select file_name from cdb_data_files;
FILE_NAME ----------------------------------------------------------------------- /oradata/cdb1/pdb11/system01.dbf /oradata/cdb1/pdb11/sysaux01.dbf /oradata/cdb1/pdb11/pdb11_users01.dbfSQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------------------
/oradata/cdb1/pdb11/system01.dbf /oradata/cdb1/pdb11/sysaux01.dbf /oradata/cdb1/pdb11/pdb11_users01.dbfSQL>
SYS用户下测试
[oracle@db1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 14 16:00:51 2016
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> create table all_data(name varchar2(20),is_pdb varchar2(10));
Table created.
SQL> insert into all_data values('cdb','N');
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set container=PDB11;
Session altered.
SQL> create table all_data(name varchar2(20),is_pdb varchar2(10));
Table created.
SQL> insert into all_data values('pdb11','Y');
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set container=PDB12;
Session altered.
SQL> create table all_data(name varchar2(20),is_pdb varchar2(10));
Table created.
SQL> insert into all_data values('pdb12','Y');
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB11 READ WRITE NO
4 PDB12 READ WRITE NO
SQL> select * from containers(all_data) where con_id in (1,3,4);
NAME IS_PDB CON_ID
-------------------- ---------- ----------
pdb12 Y 4
pdb11 Y 3
cdb N 1
SQL>
Common用户测试SQL> create user c##ohsdba identified by oracle;
User created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB11 READ WRITE NO
4 PDB12 READ WRITE NO
SQL> grant dba to c##ohsdba container=all;
Grant succeeded.
SQL>
SQL> conn c##ohsdba/oracle@127.0.0.1:/cdb1
Connected.
SQL> create table ohs(name varchar2(20));
Table created. SQL> insert into ohs values('cdb1');
1 row created.
SQL> commit;
Commit complete.
SQL> conn c##ohsdba/oracle@127.0.0.1:/pdb11
Connected.
SQL> create table ohs(name varchar2(20));
Table created.
SQL> insert into ohs values('pdb11');
1 row created.
SQL> commit;
Commit complete.
SQL> conn c##ohsdba/oracle@127.0.0.1:/pdb12
Connected.
SQL> create table ohs(name varchar2(20));
Table created.
SQL> insert into ohs values('pdb12');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from containers(c##ohsdba.ohs);
NAME CON_ID
-------------------- ----------
pdb12 4
pdb11 3
cdb1 1
SQL> conn c##ohsdba/oracle@127.0.0.1:/cdb1
Connected.
SQL> select * from containers(ohs);
NAME CON_ID
-------------------- ----------
pdb12 4
pdb11 3
cdb1 1
SQL>
删除CDB$ROOT中表再测试(无法访问)SQL> show user
USER is "SYS"
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB11 READ WRITE NO
4 PDB12 READ WRITE NO
SQL> drop table c##ohsdba.ohs;
Table dropped.
SQL> select * from containers(c##ohsdba.ohs);
select * from containers(c##ohsdba.ohs)
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn c##ohsdba/oracle@127.0.0.1:/cdb1
Connected.
SQL> create table ohs(name varchar2(20));
Table created.
SQL> conn / as sysdba
Connected.
SQL> select * from containers(c##ohsdba.ohs);
NAME CON_ID
-------------------- ----------
pdb12 4
pdb11 3
SQL>
小结：CDB和每个PDB中必须存在同样的用户同样视图才能正常访问
Reference
http://docs.oracle.com/database/121/NEWFT/chapter12102.htm#NEWFT507
