We help IT Professionals succeed at work.

usign db link to query  remote database and create  a view

maverickxx asked
I have to create a view  which pulls data from  multiple remote databases . I have the logic for queries for each of the  databases. I need to combine the( basically union) the results of the query to create  a view

Pseudo code is below

query1 - remote Database1 (RDB1)
 select a,b,c from  x ,  y,  z where < lot of conditions>...

 Query2  : Remote database :RDB2

Select a,b , c from  P, q,r,s,t where  <  various conditions>

Query3  Remotedata base 3: RDB3

Select  a, b , c  from  D,E,F,G,H,I where < conditions>

I need a create a  view in local database ( let us call it LDB)  which basically unions three queries   mentioned above and creates a view.

Assuming thee database links are  created  say dl1, dl2, dl3  how do I do this?

THanks in advance

Watch Question

create database link rdb1L   (..)  using 'RDB1'
create database link rdb2L   (..)  using 'RDB2'
create database link rdb3L   (..)  using 'RDB3'

SELECT  a,b,c from x@rdb1L, y@rdb1L , z@rdb1L where ...
SELECT a,b,c, from p@rdb2L, ...  etc

create view v_rdb1 as  (qyery1)  at rdb1
create view v_rdb2 as  (qyery2)  at rdb2
create view v_rdb3 as  (qyery3)  at rdb3

select * from v_rdb1@rdb1L
select * from v_rdb2@rdb2L
select * from v_rdb3@rdb3L

1) there is a maximum of open database links in 1 session (i don't know exact
2) depending on the data it might be interesting to hint oracle to use another then the current database as driving database (DRIVING SITE hint)
Most Valuable Expert 2012
Distinguished Expert 2019

You realize that if one of the databases is down the view will fail.

Have you thought of local Materialized Views from the remote databases the create a view based on them?


Thnaks flow