Problems with snapshot replication
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Problems with snapshot replication

  1. #1
    Join Date
    Feb 2008
    Posts
    1

    Problems with snapshot replication

    I have a table DA_TEST_TABLE_NOPK (this table is without primary keys), and I want to replicate this table into another db using snapshot replication, so i create logs on master side:

    CREATE MATERIALIZED VIEW LOG ON DA_TEST_TABLE_NOPK
    TABLESPACE DA_LOGS
    NOLOGGING
    WITH ROWID
    EXCLUDING NEW VALUES

    Then I create a table DA_TEST_TABLE_NOPK on the replicated side(with the same structure as in master side), and use matview with prebuilt option. But I cant create matview(because of error ORA-12014: table 'DA_TEST_TABLE_NOPK' does not contain a primary key constraint)

    CREATE MATERIALIZED VIEW DA_TEST_TABLE_NOPK
    ON PREBUILT TABLE
    REFRESH FAST ON DEMAND
    AS
    SELECT * FROM DA_TEST_TABLE_NOPK@master_side;
    (master_side -db link to master side )

    When I try to create matview 'with rowid', I recieved another error (ORA-12058: materialized view cannot use prebuilt table). So what can you suggest to solve this problem (replicate table without primary key with prebuilt table). Thank you for your's attention

  2. #2
    Join Date
    Feb 2008
    Posts
    8
    you can use

    CREATE MATERIALIZED VIEW name
    build immediate
    refresh fast on demand
    with rowid
    as
    select * from master_table@dblink

  3. #3
    Join Date
    Feb 2008
    Posts
    8

    a possible way

    CREATE MATERIALIZED VIEW DA_TEST_TABLE_NOPK
    BUILD IMMEDIATE
    REFRESH FAST ON DEMAND
    WITH ROWID
    AS
    SELECT * FROM DA_TEST_TABLE_NOPK@master_side;
    (master_side -db link to master side )

  4. #4
    Join Date
    Feb 2008
    Posts
    8
    CAN YOU EXECUTE OF THIS WAY

    CREATE MATERIALIZED VIEW DA_TEST_TABLE_NOPK
    BUILD IMMEDIATE
    REFRESH FAST ON DEMAND
    WITH ROWID
    AS
    SELECT * FROM DA_TEST_TABLE_NOPK@master_side;

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Neither one of your three posts answers poster's question.
    Have you read the original post?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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