Oracle SQL DBLink with many tables and joins

Question

I'm trying to execute an Oracle SQL query that can hit two different database servers at once. Both servers have the same table definitions however the SQL query is hitting many tables.

Simplified Example:

with Stage2 as (select member_nbr, service_nbr from process p left outer join services s on s.service_nbr = p.service_nbr where s.date = sysdate)
select 
member_nbr, sub_nbr, service_nbr, ymdbirth
from
( select stage1.member_nbr, stage1.sub_nbr, stage1.service_nbr, stage1.ymdbirth from (select s.member_nbr, s.sub_nbr, s.service_nbr, mbr.ymdbirth 
from Stage2 
left outer join process p on stage2.service_nbr
left outer join service s on p.service_nbr = s.service_nbr
left outer join member mbr on mbr.member_nbr = s.member_nbr
where p.paid = 'N'
and s.status not in (91,92,93)) Stage1
order by stage1.member_nbr, stage1.service_nbr);

Is there a way to execute this between 2 DB instances were the query result is combined between the two?

Instance 1:
member_nbr 1
sub_nbr 1
service_nbr 1
ymdbirth 19871001

Instance 2:
member_nbr 2
sub_nbr 2
service_nbr 2
ymdbirth 20001118

Desired results:
member_nbr      sub_nbr      service_nbr      ymdbirth
1                1                1             19871001
2                2                2             20001118

Please give full details of what you mean by "two different instances". Different hardware, same hardware but different database server, same database server but different database, etc, etc. Please be exact. — MatBailie
– MatBailie, Commented Sep 23 at 13:31
I'd expect a column named ymdbirth to have year-month-day dates. Column data type? — jarlh
– jarlh, Commented Sep 23 at 13:44
of course there is. write two query blocks, one for each dblink, then union all them together. — Paul W
– Paul W, Commented Sep 23 at 14:56

Sayan Malakshinov · Accepted Answer · 2025-09-24 23:38:52Z

1

Probably in this case it would be better to create the same view on both databases, so then you could easily write queries like:

select * from my_view@db1
union all
select * from my_view@db2

You can even include DB_NAME into your view, so you could easily understand where is your data from, like this:

create or replace my_view as
select
   SYS_CONTEXT('USERENV', 'DB_NAME') as db_name
...

answered Sep 24 at 23:38

Sayan Malakshinov

8,7501 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL DBLink with many tables and joins

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related