Re: Using materialized view on 2 databases

"kumar" <sgnerd_at_yahoo.com.sg> wrote in message news:d13fd832.0311040412.5fa89d1d_at_posting.google.com...
> Hello friends,
>
> Is it possible to create a materialized view on tables that are on 2
> databases on the same database server.
> I have 2 oracle 8i databases DB01 and DB02 running on a single Windows
2000
> server.
>
> Can I create a materialized view on tableA of DB01 and tableB of DB02?
>
> Can anyone please state an example or a link of the example. While I
> searched the documentation, it is all about the tables in same database.

It just requires some lateral thinking, and a working Oracle Net setup (ie, tnsnames, listener.ora, sqlnet.ora etc etc etc). What I'll do here is have two databases, one called 'lx92' and one 'oemrep'. I'll have Scott created in both, with alll his usual tables. I'll create the materialized view in oemrep AND in lx92, because I might want to get at the data joining both database when connected to either. That means I'll have to create database links in both, cross-referencing each other. And, because I don't want to change my select statement syntax just because I happen to be connected to one database or another, I'll create it as a select from two database links (which is what you'd do anyway if you were selecting from a third database, referencing these two).

You'll need first to create those database links, as follows:

--created in both databases. We want to be agnostic about which one you'll end up selecting from.
--'lx92' and 'oemrep' are the tnsnames aliases, and in fact the proper instance names too
--'l92' and 'orep' are used as the link names to avoid encounterinf ORA-2082 (loopbacks caused
--by having a link with the same name as the instance you're connected to.

create database link l92
connect to scott identified by tiger
using 'lx92';

create database link orep
connect to scott identified by tiger
using 'oemrep';

Now just check that each link works:

--Whilst logged on to lx92
select * from dept_at_oemrep

--Whilst logged on to oemrep
select * from emp_at_lx92

Now put it together:

create materialized view blah as
select l.empno, l.ename, l.job, o.deptno, o.dname from emp_at_l92 l, dept_at_orep o
where o.deptno=l.deptno;

select * from blah;

Works just fine, and it matters not one bit whether lx92 is on one box, oemrep on another; the two are on the same machine; or if the statements issued above are done on a cliient or on the server(s). It'll even work when one database is, say, an 8i database and the other is 9i. Actually, it just so happens that for the above example, lx92 is running on my linux box, and oemrep is running on my Windows box -though both are version 9.2

Regards
HJR
> Please shed some light.
>
> Regards
> Kumar
Received on Tue Nov 04 2003 - 07:39:55 CST