By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,501 Members | 1,859 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,501 IT Pros & Developers. It's quick & easy.

Snapshot Materialized view : how to ?

P: n/a
Hi

I'm a bit lost with snapshot creation, so can I have an easy (but with
all details : *.ora, user grants, *.sql, db links, ...) example of
snapshot between :

Server1 :
instance : I1
oracle user : user1
table : user1.table1

Server2 :
instance : I2
oracle user : user2
snapshot : user2.table1 as select * from user1.table1;

Thank you for your examples.

Bernard
bs*******@free.fr
http://bsegonnes.free.fr
Jul 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
bs*******@free.fr (Bernard Segonnes) wrote in message news:<cc*************************@posting.google.c om>...
Hi

I'm a bit lost with snapshot creation, so can I have an easy (but with
all details : *.ora, user grants, *.sql, db links, ...) example of
snapshot between :

Server1 :
instance : I1
oracle user : user1
table : user1.table1

Server2 :
instance : I2
oracle user : user2
snapshot : user2.table1 as select * from user1.table1;

Thank you for your examples.

Bernard
bs*******@free.fr
http://bsegonnes.free.fr


Hi,
Please do the following.

Locate Server1 and get the Tnsnames entry from the Tnsnames.ora file.

Paste this entry in the Tnsnames.ora file of the server2.

I am assuming that you are trying to connect to server1 from server2.

Please find out the schema name and password for User1.

assuming userid= user1 and password =password.
Please query the following against the database on sever1.
Global Database Name would be the output from Select * from
global_name.
Service Name would be the output from Select * from v$database.

Assuming that the above queries fetched me the following results.

Global Database Name = sap.patni.com
Service Name = sap

You should have a create database link priviledge to create the link.
Please ask your DBA to provide it to you.

Please run the following to create the link.

create database link sap.patni.com
connect to user1 identified by password
using 'sap';

I think creating a snapshot is easier than making it work through a
database link. :-)

Aamir
Jul 19 '05 #2

P: n/a
mo************@patni.com (Mohammad Aamir) wrote in message news:<46**************************@posting.google. com>...
bs*******@free.fr (Bernard Segonnes) wrote in message news:<cc*************************@posting.google.c om>...
Hi

I'm a bit lost with snapshot creation, so can I have an easy (but with
all details : *.ora, user grants, *.sql, db links, ...) example of
snapshot between :

Server1 :
instance : I1
oracle user : user1
table : user1.table1

Server2 :
instance : I2
oracle user : user2
snapshot : user2.table1 as select * from user1.table1;

Thank you for your examples.

Bernard
bs*******@free.fr
http://bsegonnes.free.fr


Hi,
Please do the following.

Locate Server1 and get the Tnsnames entry from the Tnsnames.ora file.

Paste this entry in the Tnsnames.ora file of the server2.

I am assuming that you are trying to connect to server1 from server2.

Please find out the schema name and password for User1.

assuming userid= user1 and password =password.
Please query the following against the database on sever1.
Global Database Name would be the output from Select * from
global_name.
Service Name would be the output from Select * from v$database.

Assuming that the above queries fetched me the following results.

Global Database Name = sap.patni.com
Service Name = sap

You should have a create database link priviledge to create the link.
Please ask your DBA to provide it to you.

Please run the following to create the link.

create database link sap.patni.com
connect to user1 identified by password
using 'sap';

I think creating a snapshot is easier than making it work through a
database link. :-)

Aamir

Actually database links are a prerequisite for snapshots.
So what are you talking about?

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #3

P: n/a
Thanks for your answers.

After 3 days of reshearches I've finaly created my snapshot. It
wasn't really clear from Oracle PDF documentation !

- I didn't knew a database link was required to have a snapshot.
- I didn't knew SYS couldn't create a database link (nor snapshot) for
another user.
- I didn't knew the 'dual' stuff.
- Oracle in not case sensitive when you type commands. But it IS case
sensitive for the ORACLE_SID.
- etc...

I wish the next Oracle version will have a better documentation for
beginer :-)

So here is the few lines I was looking for (for 3 days !) :

connect sys/sys as sysdba;
grant create database link to user2;
grant create materialized view to user2;
connect user2/user2;
create database link I1 connect to user1 identified by user1 using
'I1';
create materialized view testmv as select * from
user1.myTable@I1,dual;
Jul 19 '05 #4

P: n/a
bs*******@free.fr (Bernard Segonnes) wrote in message news:<cc*************************@posting.google.c om>...
Hi

I'm a bit lost with snapshot creation, so can I have an easy (but with
all details : *.ora, user grants, *.sql, db links, ...) example of
snapshot between :

Server1 :
instance : I1
oracle user : user1
table : user1.table1

Server2 :
instance : I2
oracle user : user2
snapshot : user2.table1 as select * from user1.table1;

Thank you for your examples.

Bernard
bs*******@free.fr
http://bsegonnes.free.fr
Hi,
Please do the following.

Locate Server1 and get the Tnsnames entry from the Tnsnames.ora file.

Paste this entry in the Tnsnames.ora file of the server2.

I am assuming that you are trying to connect to server1 from server2.

Please find out the schema name and password for User1.

assuming userid= user1 and password =password.
Please query the following against the database on sever1.
Global Database Name would be the output from Select * from
global_name.
Service Name would be the output from Select * from v$database.

Assuming that the above queries fetched me the following results.

Global Database Name = sap.patni.com
Service Name = sap

You should have a create database link priviledge to create the link.
Please ask your DBA to provide it to you.

Please run the following to create the link.

create database link sap.patni.com
connect to user1 identified by password
using 'sap';

I think creating a snapshot is easier than making it work through a
database link. :-)

Aamir
Jun 27 '08 #5

P: n/a
mo************@patni.com (Mohammad Aamir) wrote in message news:<46**************************@posting.google. com>...
bs*******@free.fr (Bernard Segonnes) wrote in message news:<cc*************************@posting.google.c om>...
Hi

I'm a bit lost with snapshot creation, so can I have an easy (but with
all details : *.ora, user grants, *.sql, db links, ...) example of
snapshot between :

Server1 :
instance : I1
oracle user : user1
table : user1.table1

Server2 :
instance : I2
oracle user : user2
snapshot : user2.table1 as select * from user1.table1;

Thank you for your examples.

Bernard
bs*******@free.fr
http://bsegonnes.free.fr

Hi,
Please do the following.

Locate Server1 and get the Tnsnames entry from the Tnsnames.ora file.

Paste this entry in the Tnsnames.ora file of the server2.

I am assuming that you are trying to connect to server1 from server2.

Please find out the schema name and password for User1.

assuming userid= user1 and password =password.
Please query the following against the database on sever1.
Global Database Name would be the output from Select * from
global_name.
Service Name would be the output from Select * from v$database.

Assuming that the above queries fetched me the following results.

Global Database Name = sap.patni.com
Service Name = sap

You should have a create database link priviledge to create the link.
Please ask your DBA to provide it to you.

Please run the following to create the link.

create database link sap.patni.com
connect to user1 identified by password
using 'sap';

I think creating a snapshot is easier than making it work through a
database link. :-)

Aamir

Actually database links are a prerequisite for snapshots.
So what are you talking about?

Sybrand Bakker
Senior Oracle DBA
Jun 27 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.