Monday, January 13, 2014

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 )

39 Comments:

Blogger jake george said...

Oracle Enterprise Manager Grid Control 12c
Introduction
Course overview
Key Challenges for Administrators
What Is Enterprise Manager Cloud Control?
Built-in and Integrated Manageability
Configuration Management
Reviewing Enterprise Manager Core Concepts
Review Oracle Enterprise Manager architecture
http://www.21cssindia.com/courses/oracle-enterprise-manager-grid-control-12c-228.html
21st Century providing Online training and support on All Technologies. If you are seeking training and support you can reach me on 91-9000444287. Online training by real time Experts. Call us 001-309-200-3848 for online training

10:29 PM  
Blogger vasudha dharani said...

Oracle Access Management Online Training
http://www.21cssindia.com/courses/oracleaccessmanagement-online-training-5.html
Introduction to Identity and Access Management Systems
Introduction to Oracle Access Manager
Installing Oracle Access Manager Identity System
Configuring Identity System Object Classes
Creating Derived Attributes
Configuring Tabs, Profile Pages, and Panels
Configuring Access Controls in Identity System
Access System Configuration
Configuring Policy Domains and Policies
Configuring User Authorization
Managing Access Servers
Configuring Single Sign-On and Integrating with Oracle WL Single Sign-On
If you are seeking training and support you can reach me on 91-9000444287

11:33 PM  
Blogger jake george said...

Oracle 12c Grid control: Oracle 12c Grid control Training, Oracle 12c Grid control Online Training, Oracle 12c Grid control Corporate Training, Best Oracle 12c Grid control Training, Expert Oracle 12c Grid control Training
http://www.21cssindia.com/courses/oracle-enterprise-manager-grid-control-12c-228.html
Oracle Enterprise Manager Grid Control 12c
Introduction
Course overview
Key Challenges for Administrators
What Is Enterprise Manager Cloud Control?
Built-in and Integrated Manageability
Configuration Management
Reviewing Enterprise Manager Core Concepts
Review Oracle Enterprise Manager architecture
If you are seeking training and support you can reach me on 91-9000444287

8:37 PM  
Blogger Path Infotech said...

Thanks for sharing the information

For more info : Oracle Database Training

11:49 PM  
Blogger Path Infotech said...

This comment has been removed by the author.

11:49 PM  
Blogger jake george said...

Oracle Access Management Online Training, ONLINE TRAINING – IT SUPPORT – CORPORATE TRAINING http://www.21cssindia.com/courses/oracleaccessmanagement-online-training-5.html The 21st Century Software Solutions of India offers one of the Largest conglomerations of Software Training, If you’re serious about a career in IT, 21st Century would like to provide you a guidance don’t hesitate to organize a free demo session. For any further information regarding the courses once go through our website Visit: http://www.21cssindia.com | Call Us +917386622889 - +919000444287 - contact@21cssindia.com

10:49 PM  
Blogger Daniel Mason said...

It was really a nice article and i was really impressed by reading this article We are also giving all software Course Online Training.The oracle Online Training is one of the leading Online Training institute in the world.

10:33 AM  
Blogger Qa Tools said...

This comment has been removed by the author.

10:46 AM  
Blogger Qa Tools said...


This information which you provided is very much useful for us.It was very interesting and useful for Oracle online training learners.We also providing qa online training institute in USA.

10:48 AM  
Blogger S M Consultants said...

Thanks for this great information. That’s a awesome article you posted. I found the post very useful as well as interesting.
S&M consultants

3:20 AM  
Blogger Mathew Stephen said...

There are lots of information about oracle have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get to the next level in oracle. Thanks for sharing this.

Oracle Training Center in Chennai | Oracle Training | hadoop training in chennai

3:48 AM  
Blogger Yasmin Priya said...

Hi, I have read your blog. You did really great job. I gathered lots of new information related to oracle from your blog. Thanks for sharing this informative blog.Oracle Apps Training in Chennai

3:25 AM  
Blogger Diya Patel said...

Best iOS Training Institute In Chennai It’s too informative blog and I am getting conglomerations of info’s about Oracle interview questions and answer .Thanks for sharing, I would like to see your updates regularly so keep blogging.

5:12 AM  
Blogger caroline jesi said...

Everything is fine, am happy about your blog. Thanks admin for sharing the unique content, you have done a great job I appreciate your effort and I hope you will get more positive comments from the web users.
Regards,
Oracle Training in Chennai|Oracle DBA Training in Chennai

5:11 AM  
Blogger Fathima Fazal said...

Very useful information and i am planning to do ios training in chennai can anyone help me out in choosing best ios training institute in chennai. ?

11:20 PM  
Blogger kanchana said...

Your blog is awesome..You have clearly explained about it ...Its very useful for me to know about new things..Keep on blogging..
Hadoop training in chennai

5:48 AM  
Blogger deeksha said...

your blog is really contains information are too good and it is useful for me to enrich your knowledge.


hadoop training in chennai

10:37 PM  
Blogger kanchana said...

Nice..You have clearly explained about the concept..Its very useful for me to understand..Keep on sharing..
Hadoop training in chennai

10:44 PM  
Blogger Brn Infotech said...

I just want to say that all the information you have given here is awesome. Thank you
best ios training institute

10:04 PM  
Blogger Camellia Canan said...

These provided information was really so nice,thanks for giving that post and the more skills to develop after refer that post. Your articles really impressed for me,because of all information so nice.

SAP training in Chennai

7:08 AM  
Blogger Camellia Canan said...


Thanks for the good words! Really appreciated. Great post. I ve been commenting a lot on a few blogs recently, but I had nt thought about my approach until you brought it up.

SAP training in Chennai

6:38 AM  
Blogger rakesh said...

thanku for sharing this nice posts..
SAS Institute introduced the SAS Certified Professional Program,training proper understanding of how the SAS software works. Among the five certification programs that SAS Institute has come up with, SAS training can be considered as the entry point into the big data and the data analytics industry.
SAS online training in hyderabad

9:23 PM  
Blogger big data trunk1 said...


Thank you for sharing such great information !
big data classroom training
hadoop ónlinÉ training
free big data bootcamp

hadoop big data videos

Big data QA Tester training
Big data Analyst training

9:20 PM  
Blogger Suseela Susiee said...




Great and useful article. Creating content regularly is very tough. Your points are motivated me to move on.


SEO Company in Chennai

7:14 AM  
Blogger rakesh said...

nice information provided...
SAS Institute introduced the SAS Certified Professional Program,training proper understanding of how the SAS software works. Among the five certification programs that SAS Institute has come up with, SAS training can be considered as the entry point into the big data and the data analytics industry.
SAS online training in hyderabad

3:30 AM  
Blogger Suseela Susiee said...

Great and useful article. Creating content regularly is very tough. Your points are motivated me to move on.


SEO Company in Chennai

6:57 AM  
Blogger Rakesh S said...

Thanks for providing the great blog..
Informatica training, in the recent times has acquired a wide scope of popularity amongst the youngsters at the forefront of their career.
Informatica online training in hyderabad


10:27 PM  
Blogger Nikshitha S said...

Excellent post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
SAS Training in Chennai | SAS Course in Chennai

4:59 AM  
Blogger Rohith said...

Awesome..You have clearly explained …Its very useful for me to know about new things..Keep on blogging..
seo institute in chennai

4:55 AM  
Blogger Rakesh S said...

Thanku for sharing this valuable information..
SAS Institute introduced the SAS Certified Professional Program.
SAS online training in hyderabad

9:15 PM  
Blogger kitharington said...


Hi,I hope to really understand your wonderful information.Thank you so much.
Tableau Training | Hadoop Training | Informatica Training

10:49 PM  
Blogger Aashi siva said...


This idea is mind blowing. I think everyone should know such information like you have described on this post. Thank you for sharing this explanation.Your final conclusion was good. We are sowing seeds and need to be patiently wait till it blossoms.

Corporate Training in Chennai

2:31 AM  
Blogger Thanu Abi said...

You did a great job.. Thanks a lot for sharing this useful informative post with us.. Keep on blogging like this informative post with us, to develop my career in the right way.

Branding Services in Chennai

2:30 AM  
Blogger Rakesh S said...

This Oracle Table defines The PDB to another transfers of data..
Hadoop training in hyderabad.All the basic and get the full knowledge of hadoop.
hadoop training in hyderabad

10:22 PM  
Blogger Suseela Susiee said...






What an awesome post, I just read it from start to end. Learned something new after a long time.



Cloud Computing training in Chennai

Salesforce training in Chennai

VMware training in Chennai

AWS training in Chennai

6:31 AM  
Blogger Jupitor Berg said...

Thank for sharing this useful information;
It is very helpful to who are wanted to learn or update their knowledge on oracle 12c DBA through Oracle 12c DBA online training or offline training.

1:06 AM  
Blogger Jeffy said...

Thank you for sharing such a nice and interesting blog with us. Hope it might be much useful for us. keep on updating...!!
seo company in india
digital marketing company in india
seo company in chennai
digital marketing company in chennai

9:50 PM  
Blogger yasar said...

very nice and informative blog
big data projects chennai
mobile computing projects chennai
cloud computing projects chennai
secure computing projects chennai

1:36 AM  
Blogger sathya said...

Wonderful bloggers like yourself who would positively reply encouraged me to be more open and engaging in commenting. So know it's helpful..

Dataware Housing Training in Chennai

10:12 PM  

Post a Comment

<< Home