SNAPSHOTS not properly created
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SNAPSHOTS not properly created

  1. #1
    Join Date
    Jan 2001
    Posts
    26

    Angry

    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width