SNAPSHOTS not properly created

[jdjoshi]

Folks;

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.

[sambavan]

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


Sam

[julian]

Here is an example of how you should do it:

MASTER site:

CREATE SNAPSHOT LOG ON TABLE_NAME
TABLESPACE REPLICA
PCTFREE 60 PCTUSED 30 MAXTRANS 255
STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
WITH ROWID
EXCLUDING NEW VALUES;

SNAPSHOT SITE:

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)
TABLESPACE ADS_DATA
BUILD IMMEDIATE
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)
AS
SELECT * FROM INSTRU@MASTER;

This works for me. 1/1440 is 1 minute. All the date gets replicated every minute.