Home » Server Options » Multitenant » Share data between PDB's in the same CDB
Share data between PDB's in the same CDB [message #672638] Sat, 20 October 2018 22:03 Go to next message
goancea
Messages: 3
Registered: January 2013
Location: Bucharest
Junior Member
Dear all,

I have the following question: can data from one PDB be read by other PDB, in the same CDB?

We currently have separate databases and a huge data export/import procedure (export/import serialization/deserialization procedures, daemons to load/unload the data in MQS, queue's between) for a huge quantity of data every day.

If granting select for one PDB user for tables in another PDB in the same container is possible, we can skip the export/import complicated, time and resource consuming procedure.

Thank you!

George
Re: Share data between PDB's in the same CDB [message #672642 is a reply to message #672638] Sun, 21 October 2018 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

DATABASE LINK?
Re: Share data between PDB's in the same CDB [message #672713 is a reply to message #672642] Thu, 25 October 2018 06:49 Go to previous messageGo to next message
goancea
Messages: 3
Registered: January 2013
Location: Bucharest
Junior Member
Hi Michel,

Thank you for your post.

A select from v$version returns:

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 IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

We have different applications, many of the in the same database and the same schema and some of them in other databases (other machines, other databases and schemas).
The second application types uses data from the first ones.

I'm thinking to consolidate them in the same container as pluggable databases and to skip the export import part (which is a huge data transfer each day).
Is database links a good solution, as we have many tables with hundreds of milions of records to be queried?

My question was if from one pluggable database in a container you can access the data in another pluggable database in the same container.

Is the database link the only solution? How reliable is it if we have many queries to be done via dblink (on big tables)?

Thank you!

George
Re: Share data between PDB's in the same CDB [message #672725 is a reply to message #672713] Thu, 25 October 2018 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is the database link the only solution?
Give the architecture you choose, yes.

Quote:
How reliable is it if we have many queries to be done via dblink (on big tables)?
How do you do it now?

Re: Share data between PDB's in the same CDB [message #672728 is a reply to message #672725] Thu, 25 October 2018 12:37 Go to previous messageGo to next message
goancea
Messages: 3
Registered: January 2013
Location: Bucharest
Junior Member
Now we replicate the data using an inhouse mechanism (serialization, putting the data in MQS, import the data in the other database, deserialize and import the data in similar tables).

I want to skip this export/import procedure because is time consuming, generate errors and missing data.

The best approach I guess is to put all applications in one database.

How big a database can be?
Re: Share data between PDB's in the same CDB [message #672729 is a reply to message #672728] Thu, 25 October 2018 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The best approach I guess is to put all applications in one database.
Yes.

Quote:
How big a database can be?
Exabytes.

Database Reference, Physical Database Limits

Quote:
Database files, Maximum per database, 65533
The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks.
Re: Share data between PDB's in the same CDB [message #672733 is a reply to message #672728] Thu, 25 October 2018 14:07 Go to previous message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
goancea wrote on Thu, 25 October 2018 18:37
Now we replicate the data using an inhouse mechanism (serialization, putting the data in MQS, import the data in the other database, deserialize and import the data in similar tables).

I want to skip this export/import procedure because is time consuming, generate errors and missing data.

The best approach I guess is to put all applications in one database.

How big a database can be?
You need to be a bit careful about consolidating many databases into one. You can convert many databases into pluggable containers in a single container database no problem, but you need to think about the instance as well. Can the one instance really service all the PDBs as well as when they had an instance each? For example, if any if them have redo generation as a performance bottleneck, then converting to PDBs will be a disaster.

Using database links will work just as well between separate databases as it will between separate PDBs in one database. Better try it that way first, and perhaps save the expense and hassle of going to mutlitenant.
Previous Topic: Refresh exisitng PDB from local PDB
Next Topic: Best Multitenant Databaes
Goto Forum:
  


Current Time: Sun Dec 08 17:45:32 CST 2019