Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Unanswered: Using Multi-Master Replication without primary keys

    Is that possible, to use multi-master Oracle replication, with tables that does not have primary keys (maybe using ROWID) ?

    I've tried to search for such an option in the docs, but could not find such..

    Idea, anyone,

    Thanks,

    Tal Olier (otal@mercury.co.il)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using Multi-Master Replication without primary keys

    Originally posted by tal_olier
    Is that possible, to use multi-master Oracle replication, with tables that does not have primary keys (maybe using ROWID) ?

    I've tried to search for such an option in the docs, but could not find such..

    Idea, anyone,

    Thanks,

    Tal Olier (otal@mercury.co.il)
    Sounds unlikely - if you have 2 or more identical records, how will Oracle know which to replicate? ROWID is no use between databases, because it is a physical address. Why not just add primary keys to your tables?

  3. #3
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Re: Using Multi-Master Replication without primary keys

    Originally posted by andrewst
    Sounds unlikely - if you have 2 or more identical records, how will Oracle know which to replicate? ROWID is no use between databases, because it is a physical address. Why not just add primary keys to your tables?

    - Why doesn’t oracle replication creates those PK for me as it does with internal triggers?
    - Why not make a 'complete refresh’?
    - Why not calculate the delta himself?

    If I will not find another alternative, that's what I'll do. But for now I'm trying to find another solution.

    Thanks,

    Tal.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using Multi-Master Replication without primary keys

    Originally posted by tal_olier
    - Why doesn’t oracle replication creates those PK for me as it does with internal triggers?
    - Why not make a 'complete refresh’?
    - Why not calculate the delta himself?

    If I will not find another alternative, that's what I'll do. But for now I'm trying to find another solution.

    Thanks,

    Tal.
    I can't add much to this, as I am no expert on Oracle Replication. But I would have thought that having primary keys on every table would be the FIRST resort, not the last - even if you just add a surrogate PK to each table (not tricky).

    Being unable to identify a particular record is heinous enough on a standalone database; if Oracle is expected to keep 2 or more databases in sync when it can't even identify specific records, that compounds the problem (it is not sufficient for Oracle to determine that the record does/does not exist in the replicated database, it has to make sure it holds the same number of duplicates).

  5. #5
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    Re: Using Multi-Master Replication without primary keys

    Originally posted by tal_olier
    - Why doesn’t oracle replication creates those PK for me as it does with internal triggers?
    - Why not make a 'complete refresh’?
    - Why not calculate the delta himself?

    If I will not find another alternative, that's what I'll do. But for now I'm trying to find another solution.

    Thanks,

    Tal.
    PKs are constraints and are matter of schema design.

    Let oracle do it's job, and be itself: an rdbms.
    Let the DBAs be theirself: design the database, define PKs, FKs, check Contraints, and so on.
    Who have to decide if SSN have to be the PK of your cust table intead of Cust_id ? Surely NOT oracle.

    Replication is a feature not a self made world: you have to learn what and how to use it for your targets. But it seems to me you should start with an in deep refresh/reload of your knowledge about the relational theory.

    Finally: if you do not have a path to find a better PK, you can include all the fields of the table in the PK constraint. It's a valid key for all tables, except in the case you have twin rows, but you cannot distinguish each other, so that they are the same.

    Good luck.
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

Posting Permissions

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