I am trying to create a snapshot on 8.1.7.
However, after creating the snapshot, in the user_snapshot view, the table_name and the name appears the same.
Normally, once the snapshots are created successfully, SNAP$_ is prefixed to the table name in the table_name col. However that does not happen.
This is preventing me from granting select privs on the snapshot to another user.
Has anyone experienced this ? Any ideas why SNAP$_ is missing ? Any causes for this ... ?
Thanks in advance.
Extract from metalink
Begining of Oracle 8.1 oracle eliminates SNAP$_, MVIEW$_ and the VIEW that was the snapshot.
As of 8.1, Oracle creates the base table which IS the snapshot. This base table will have the name that you have specified during the create snapshot process.
The index I_SNAP$_ is still created but now corresponds
to the primary key (if snapshot was created with primary key, which is default) OR corresponds to the HIDDEN rowid column M_ROW$$ (if snapshot was created with rowid)."
RTD: Oracle Replication
Life is a journey, not a destination!
Here is an example of how you should do it:
CREATE SNAPSHOT LOG ON TABLE_NAME
PCTFREE 60 PCTUSED 30 MAXTRANS 255
STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
EXCLUDING NEW VALUES;
CREATE SNAPSHOT TABLE_NAME
PCTFREE 10 PCTUSED 40 MAXTRANS 255
STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
USING INDEX TABLESPACE ADS_INDX
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 1024K NEXT 1024K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645
FREELISTS 1 FREELIST GROUPS 1 )
REFRESH FORCE WITH ROWID
START WITH sysdate
NEXT sysdate + (1/1440)
SELECT * FROM INSTRU@MASTER;
This works for me. 1/1440 is 1 minute. All the date gets replicated every minute.
Click Here to Expand Forum to Full Width