SNAPSHOT ERROR

[Arsalan]

Sir

question  (1) should i create snapshot or meterailazed view for data warehouse which one is more uesful ?

problem in snapshot


I want to create snapshot from remote db

1-create database link dwsig_link connect to "sigtasad" identified by "sigtas" using 'dwsig';

2-SQL> create snapshot log on ACCT_REPORT;
create snapshot log on ACCT_REPORT
*
ERROR at line 1:
ORA-00942: table or view does not exist

then i give link of db then it give other error


SQL> create snapshot log on sigtasad.ACCT_REPORT@dwsig_link;
create snapshot log on sigtasad.ACCT_REPORT@dwsig_link
                                           *
ERROR at line 1:
ORA-00949: illegal reference to remote database
----------------------------------------------------------------------------

if write select * from sigtasad.acct_report@dwsig_link;

it show record without error so the link is fine

[ErmanArslansOracleBlog]

Snashot logs are created locally on the base. Not using db link. It should be created on the database that the base table resides.

8 Kas 2016 08:13 tarihinde "Arsala saleem [via Erman Arslan's Oracle Forum]" <[hidden email]> yazdı:

Sir

question  (1) should i create snapshot or meterailazed view for data warehouse which one is more uesful ?

problem in snapshot


I want to create snapshot from remote db

1-create database link dwsig_link connect to "sigtasad" identified by "sigtas" using 'dwsig';

2-SQL> create snapshot log on ACCT_REPORT;
create snapshot log on ACCT_REPORT
*
ERROR at line 1:
ORA-00942: table or view does not exist

then i give link of db then it give other error


SQL> create snapshot log on sigtasad.ACCT_REPORT@dwsig_link;
create snapshot log on sigtasad.ACCT_REPORT@dwsig_link
                                           *
ERROR at line 1:
ORA-00949: illegal reference to remote database
----------------------------------------------------------------------------

if write select * from sigtasad.acct_report@dwsig_link;

it show record without error so the link is fine

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/SNAPSHOT-ERROR-tp1511.html

To start a new topic under Database, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

[ErmanArslansOracleBlog]

For your info:

Materalized view and Snapshot are the same things.
Snapshot is just the old name of Materialized view.
In these days, we are using Oracle 10g, 11g or 12c mostly, so it is better to use the word Materialized view (MV)..

[Arsalan]

Thanks for reply

Ok snapshot logs we will create on locally that is correct. so

should i create snapshot or meterailazed view for data warehouse which one is more uesful  and

where materialized view log we should create on locally or db link.


Regard

[ErmanArslansOracleBlog]

Snapshot = MV

So, the answer to your question is Snapshot = MV :)

Snapshot log should be created on the instance locally.

Suppose, you have a table on DB A.
And suppose, you want to create an MV on DB B to retrieve the records from the table that resides on DB A.
Then you will create MV LOG on the table which resides on DB A.


MV LOGs are for tracking the changes for the MV refreshes.

Read oracle documentation for further info.

[Arsalan]

Great sir

so when we create materialized view log it required primary key.. so if we don't want to add primary
key so what should we do ?



SQL> create materialized view log on abcd;
create materialized view log on abcd
*
ERROR at line 1:
ORA-12014: table 'ABCD' does not contain a primary key constraint

[ErmanArslansOracleBlog]

Cause:  The CREATE MATERIALIZED VIEW LOG command was issued with the WITH
        PRIMARY KEY option and the master table did not contain a primary key
        constraint or the constraint was disabled.
Action: Reissue the command using only the WITH ROWID option, create a primary
        key constraint on the master table, or enable an existing primary key
        constraint.

Example:

SQL> create materialized view log on M1 with ROWID;

[Arsalan]

Thank you for your supports.


Be successful in your life..



Arsala Salem

[ErmanArslansOracleBlog]

Thanks for the feedback.
You can use this forum to ask questions in the future, as well :)