|
|
|
|
|
|
|
|
|
|
|
|
| Re: Materialized Views on 3 database [message #550336 is a reply to message #550328] |
Sun, 08 April 2012 14:56   |
 |
Barbara Boehmer
Messages: 9103
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
