I want to make a query to two different data databases in ORACLE.
What I tried so far is:
select *
from puigmal_admin.dba.rf_md_sectors C
left join puigmal_cad_admin.dba.a3tsec5_plp A on A.cadena = C.codi_sect;
-
That is not possible in Oracle, you need to create a dblinkuser330315– user3303152020-01-28 11:59:04 +00:00Commented Jan 28, 2020 at 11:59
-
I guess yo uare getting confused between Databases and servers. You can communicate between databases but not with 2 servers without DBlink.Ankit Bajpai– Ankit Bajpai2020-01-28 12:01:27 +00:00Commented Jan 28, 2020 at 12:01
-
@AnkitBajpai - 1, 2, more, servers is irrelevent. If you want to select from multiple databases in Oracle,a database link is needed. Period. Full stop. It doesn't make any difference what servers those databases reside on.EdStevens– EdStevens2020-01-28 12:45:39 +00:00Commented Jan 28, 2020 at 12:45
-
@EdStevens, I beg to differ. In Oracle, DBs are usually users. You can access one users table from another user or DB if they are on same server.Ankit Bajpai– Ankit Bajpai2020-01-28 12:58:01 +00:00Commented Jan 28, 2020 at 12:58
-
@AnkitBajpai - what you describe is MSSQL architecture. Not Oracle. In oracle "database" most definitely does NOT equal "user". You need to review the Oracle Concepts.EdStevens– EdStevens2020-01-28 14:43:15 +00:00Commented Jan 28, 2020 at 14:43
Add a comment
|
1 Answer
What do you call a "database"? I'm asking because people - whose background is not Oracle - tend to mix oracle "users" (schemas) with "databases".
If tables involved in query belong to users that reside in the same database, it is enough to grant (select) privilege from one user to another.
connect scott/tiger
grant select on emp to mike;
connect mike/lion
select d.dname, e.ename
from dept d join scott.emp e on e.deptno = d.deptno
^^^^^^^^^
this is a table that belongs to SCOTT who granted you SELECT privilege
If they belong to users that reside in different databases, then you need a database link.
connect mike/lion
create database link dbl_scott
connect to scott
identified by tiger
using 'orcl';
select d.dname, e.ename
from dept d join emp@dbl_scott e on e.deptno = d.deptno;
^^^^^^^^^^^^^
this is a table you're accessing over the database link
2 Comments
Ankit Bajpai
What do you exactly mean by
users that reside in different databases? Coz as far as i know users and databases are same in Oracle. Is there something else also?
Littlefoot
In Oracle, user is not the same as the database, @Ankit. A database can contain many users (every user owns its own schema whose name is equal to user's name). Read database concepts, docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/…