Oracle 12c. Accessing tables in PDB from CDB or another PDB.
In these RDBMSs a user being currently in one database can access tables in another database. For example, in MSSQL you can be in DB01 and select data from DB02 (given that you have permissions):
select * from DB02.dbo.ATABLE
It is not that simple in Oracle 12c! You cannot specify PDB before schema. You have to switch or to connect to a PDB to access data in that.
ALTER SESSION SET CONTAINER=PDB02;
select * from ASCHEMA.ATABLE;
select * from ATABLE;
But what if you need to select data from a PDB while you are in CDB or in another PDB?
Oracle documentation states: “The common user cannot query tables or views in a PDB.” 1
“Wait a second! How to query/join data from different PDBs for reports or comparisons?” you may ask.
Relax, it is still possible. The answer is – DATABASE LINK.
To make it possible you should first modify tnsnames.ora file on database server, in Oracle Home of the instance.
Add there TNS entries for PDBs you would like to access.
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = pdb01)
Second, create DB link.
create public database link PDB01 using 'PDB01';
select * from ASCHEMA.ATABLE@PDB01;
In this case the DB link was created in CDB, so common users can access data in PDB01.
If you need to allow a local user to access another PDB you should create DB link in the PDB of the local user.
You can play with public or user’s DB links to build security you need, main idea is to access PDBs via DB links.
1. Oracle® Database Administrator's Guide 12c Release 1 (12.1) , Chapter 40 Administering a CDB with SQL*Plus http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#ADMIN13616 )