Re: Using materialized view on 2 databases

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3fa7c532$0$255$ed9e5944_at_reading.news.pipex.net...
> small comment, consider the effect of GLOBAL_NAMES=TRUE in the
> init.ora/spfile.

Thanks Niall.... mine always is, so I forgot to mention it!

Wash your mouth out, however, now that you've mentioned the "s" word.

;-)

Regards
HJR
>
> --
> 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 - 15:41:37 CST