DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: snapshot

  1. #1
    Join Date
    Aug 2001
    Posts
    36
    Hi,

    while trying to create a snapshot following error occurs

    SQL> create snapshot snap123
    2 refresh fast next sysdate + 1
    3 as select * from pract1;
    as select * from pract1
    *
    ERROR at line 3:
    ORA-23413: table "SYSTEM"."PRACT1" does not have a snapshot log

    please suggest a solution to this problem.

    Regards,

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Have you create the snapshot log in the master site?

    Angel

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    When you are specifying FAST refresh with the snapshot clause it is necessary that there is a SNAPSHOT LOG created at the master site which stores all the updations,insertions and deletions which occur on the master table and this SNAPSHOT LOG is replicated on to the SNAPSHOT .

    e.g At the master site enter the following command
    create snapshot log on PRACT1;


    Regards,
    ROhit Nirkhe,Oracle DBA,APPS DBA,OCP 8i
    oracle-support@indiatimes.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    On the master site run:

    Code:
    CREATE SNAPSHOT LOG ON pract1
    TABLESPACE REPLICA 
    PCTFREE 60 PCTUSED 30 MAXTRANS 255 
    STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
    WITH PRIMARY KEY, ROWID
    INCLUDING NEW VALUES;
    Replace REPLICA with the tablespace you would like to keep your MLs in.

    Then on the snapshot site run:

    Code:
    CREATE SNAPSHOT pract1
    PCTFREE 10 PCTUSED 40 MAXTRANS 255 
    STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 
             PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
    TABLESPACE YOUR_TS_DATA
    BUILD IMMEDIATE 
    USING INDEX TABLESPACE YOUR_TS_INDX
    PCTFREE 10 INITRANS 2 MAXTRANS 255 
    STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 
            FREELISTS 1 FREELIST GROUPS 1 )
    REFRESH FAST WITH PRIMARY KEY
    START WITH trunc(sysdate) + 4/24
    NEXT trunc(sysdate+1) + 4/24
    AS
    SELECT * FROM pract1@dba_master;
    Replace YOUR_TS_DATA and YOUR_TS_INDX with the right tablespace names. Additionally, change the storage values with the ones you would like to have.

    Thus you will have the MV refreshed every morning at 4AM.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Aug 2001
    Posts
    36
    Hi all,

    Thanks a lot for such a detailed answer.

    Regards,

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