Home » Server Options » Replication » Materialized Views on 3 database (Red Hat Enterprise linux , Oracle 11gR2)
Materialized Views on 3 database [message #550315] Sun, 08 April 2012 10:28 Go to next message
syedshehzaad
Messages: 8
Registered: March 2012
Junior Member
Sorry if i have posted my question in wrong section but i was unable to decide where to post as i am a new user here

So here i come up with my question:

There is a database db1 which has user U1 in in it contains T1 as table.

Likely,

There is also another database db2 which also has a user named U2 containing table T2 in it.

Now


I want to use the concept of JOINS and Join Table T1 of database named DB1 and Table T2 of database named DB2 and access from database named DB3 using Materialized View Concept.


So guys help me out what shall i do to access tables of DB1 and DB2 from database DB3 using Materialized View.

Please Check the Attached Screen


Please Explain Steps To do this. I would be really thankkful if anyone can explain the step wise process in it.



  • Attachment: sdf.jpg
    (Size: 58.63KB, Downloaded 1377 times)
Re: Materialized Views on 3 database [message #550317 is a reply to message #550315] Sun, 08 April 2012 11:39 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think you are trying to describe materialized view replication. Have you read the relevant doc? Here:
http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#i34980

[Updated on: Sun, 08 April 2012 11:40]

Report message to a moderator

Re: Materialized Views on 3 database [message #550321 is a reply to message #550317] Sun, 08 April 2012 12:22 Go to previous messageGo to next message
syedshehzaad
Messages: 8
Registered: March 2012
Junior Member
Your link is what i am into and i just wanna know answer to my query.Can u help me out Sir .Plz read carefully my questions. I would b thankful if u can help me Smile
Re: Materialized Views on 3 database [message #550323 is a reply to message #550321] Sun, 08 April 2012 13:02 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Create your database links and then create a materialized view with a statement that SELECTs through them. What's the problem?
Re: Materialized Views on 3 database [message #550328 is a reply to message #550323] Sun, 08 April 2012 14:06 Go to previous messageGo to next message
syedshehzaad
Messages: 8
Registered: March 2012
Junior Member
Yea but can u explain it step by step please.I mean between whome shall the db link be created and what query can be written to fetch the data from 2 databases. CAN U EXPLAIN IT BRIEFLY IN STEPS PLEASE SIR !!!
Re: Materialized Views on 3 database [message #550333 is a reply to message #550328] Sun, 08 April 2012 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the link John gave?
What did you try then?

Please read OraFAQ Forum Guide and do not use IM/SMS speak.

Regards
Michel
Re: Materialized Views on 3 database [message #550336 is a reply to message #550328] Sun, 08 April 2012 14:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is a simplified partial example for you, where user u3 in database db3 creates database links to db1 and db2, then selects data from table1 in the u1 schema in db1 and table2 in the u2 schema in db2, then creates a materialized view that joins the data in the two tables, and selects from the materialized view.

Where you see ... I have left out some code for you to figure out. You will need to make sure that user u3 or whatever user you use in db3 has privileges to create a database link and privileges to create a materialized view and has been granted select privileges on the tables to be joined. Your columns and join condition may be different as you did not provide them, and this is just an example. I also did not specify how and when to refresh the materialized view or various other options.

U3@orcl_11gR2> create database link db1 ...
  2  /

Database link created.

U3@orcl_11gR2> create database link db2 ...
  2  /

Database link created.

U3@orcl_11gR2> select * from u1.table1@db1
  2  /

        ID DATA
---------- ----------------
         1 some table1 data
         2 more table1 data

2 rows selected.

U3@orcl_11gR2> select * from u2.table2@db2
  2  /

        ID DATA
---------- ----------------
         1 some table2 data
         2 more table2 data

2 rows selected.

U3@orcl_11gR2> create materialized view mv1 as
  2  select t1.data as t1_data,
  3  	    t2.data as t2_data
  4  from   u1.table1@db1 t1,
  5  	    u2.table2@db2 t2
  6  where  t1.id = t2.id
  7  /

Materialized view created.

U3@orcl_11gR2> select * from mv1
  2  /

T1_DATA          T2_DATA
---------------- ----------------
some table1 data some table2 data
more table1 data more table2 data

2 rows selected.

[Updated on: Sun, 08 April 2012 14:57]

Report message to a moderator

Re: Materialized Views on 3 database [message #550427 is a reply to message #550336] Mon, 09 April 2012 08:13 Go to previous message
syedshehzaad
Messages: 8
Registered: March 2012
Junior Member
Barbara Boehmer

Thanks a million for you Help.

Thanks to michel and john too for helping me !!!
Previous Topic: How to remove entry from dba_repgroup
Next Topic: Oracle9i Asynchronous Replication does not puch transactions automaticly
Goto Forum:
  


Current Time: Thu Mar 28 08:54:48 CDT 2024