Oracle 12c. Accessing tables in PDB from CDB or another PDB.
If you are reading this, you should know that Oracle 12c introduced the new big feature – Pluggable Data Bases. Finally Oracle has made the thing that was available in other RDBMSs for years – now you can have multiple databases run by one instance. This is available from very beginning in RDBMSs such as Sybase, DB2, MSSQL, MySQL… you name it.
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):
use DB01
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;
Or
CONNECT ASCHEMA@PDB02
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.
pdb01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb01)
)
)
Second, create DB link.
connect system/password
create public database link PDB01 using 'PDB01';
select * from ASCHEMA.ATABLE@PDB01;
Done!
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.
References:
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 )
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):
use DB01
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;
Or
CONNECT ASCHEMA@PDB02
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.
pdb01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb01)
)
)
Second, create DB link.
connect system/password
create public database link PDB01 using 'PDB01';
select * from ASCHEMA.ATABLE@PDB01;
Done!
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.
References:
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 )
posted by Mark Malakanov at 1:18 PM
17 comments
