small comment, consider the effect of GLOBAL_NAMES=TRUE in the
init.ora/spfile.
--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:3fa7abbc$0$9262$afc38c87_at_news.optusnet.com.au...
>
> "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 - 09:26:42 CST
