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

Thread: Snapshot refresh

  1. #1
    Join Date
    Mar 2002
    Posts
    46

    Snapshot refresh

    HI,

    I need information On what is the best solution for my case. Here's the initial situation:
    I'm working with ORACLE 8.1.7.4.

    There is 2 databases DB1 and DB2. In DB2, I access complicated views owned by DB1 via a DBLink IN READ ONLY. In DB2, a DBA created other views based on the complicated views in DB1. There is RLS policies on some of those views so the performance is horrible.

    What can I do?

    Here's my Idea:

    In DB1, The complicated views are based on 4 differents tables. In DB2 I want to create snapshots of those four tables. I will use simple snapshot (with log in DB1) to accelerate the refresh. Is it possible to refresh the snapshot in DB2 on commit in DB1 when the master tables changed. I need real time on this information.

    If you have an another approach don't hesitate and let me know.

    Thanks
    DONTBSHY

  2. #2
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97
    Try use MATERIALIZED VIEW.
    With ON DEMAND option.
    Márcio de Souza Almeida
    DBA Oracle / SQLServer / PostgreSQL
    Rua Cupa, 139 Apto 85 A
    Penha - São Paulo - SP
    03640-000 - Brasil
    http://www.directory.com.br

  3. #3
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    Are you running with Oracle EE? If so, you can setup
    multimaster replication for the tables in question.
    If you are running standard, then you have to use
    the Snapshot method.

    You could also write your own "Replication" with
    triggers on DB1 tables that insert into tables on
    DB2 thru the dblink. Not as clean, but ...

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    The only issue with Syncronous Replication and Triggers calling DB links is if there's a failure on the secondard DB, effectly any transaction will be rolled back on the primary DB until the Replication is removed.

    I've used both processes successfully on 8.1.7. Should be used on low volume tables though (same with triggers). COnsidering you have to wait for teh chage to commit on the other DB before the transaction completes.

    If performing many changes consider Fast refreashes on a regular basis using snapshots.
    OCP 8i, 9i DBA
    Brisbane Australia

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