Snapshot Materialized view : how to ?

[Bernard Segonnes]

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

[Mohammad Aamir]

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

[sybrandb]

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

[Bernard Segonnes]

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;

[Mohammad Aamir]

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

[sybrandb]

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