Re: ** anyway to create a primary key on table with duplicates that cannot be deleted

From: <talat.asghar_at_uk.bnpparibas.com>
Date: Thu, 20 Oct 2005 09:38:49 +0100
Message-ID: <OFA2675107.8945B1AD-ON802570A0.002DED33-802570A0.002F7FCB@bnpparibas.com>

Why wasn't a PK defined on the table when you initially added the table to replication ? Presumably you set the columns to use an underlying non-unique index which implies that the app is designed to allow duplicate values in that table. Are you experiencing deferrors due to the duplicates ?

Don't forget that any DDL on the table will require you to generate rep support.

Talat.

Internet
terrysutton_at_usa.net_at_freelists.org - 20/10/2005 00:16

Please respond to terrysutton_at_usa.net

Sent by: oracle-l-bounce_at_freelists.org

To: oracle-l

cc:

Subject: Re: ** anyway to create a primary key on table with duplicates

that cannot be deleted

I may be missing what the issue is, but assuming you mean Multi-Master Replication, here's what I would do.

In a session on each database
1. exec dbms_reputil.replication_off
2. Delete the duplicates using any one of the many queries people use to do this (you may want to create a non-unique index on what should be the primary key column before you do this)
3. Create the primary key (after dropping the non-unique index) 4. exec dbms_reputil.replication_on

--Terry

      I have a table under replication and corrupted with duplicates. I
      cannot do any update/delete operation on the table since it needs a
      primary key. I cannot create a primary key since duplicates exist.
      catch..

i tried creating index by appending rowid/rownum but no luck. Any way out of this. Cannot remove the table from replication since that needs quiescing and cannot quiese right now due to processing that will last another 4 days. Thanks for help.
(See attached file: C.htm)

This message and any attachments (the "message") is intended solely for the addressees and is confidential. If you receive this message in error, please delete it and immediately notify the sender. Any use not in accord with its purpose, any dissemination or disclosure, either whole or partial, is prohibited except formal approval. The internet can not guarantee the integrity of this message. BNP PARIBAS (and its subsidiaries) shall (will) not therefore be liable for the message if modified.

BNP Paribas Private Bank London Branch is authorised by CECEI & AMF and is regulated by the Financial Services Authority for the conduct of its investment business in the United Kingdom.

BNP Paribas Securities Services London Branch is authorised by CECEI & AMF and is regulated by the Financial Services Authority for the conduct of its investment business in the United Kingdom.

BNP Paribas Fund Services UK Limited is authorised and regulated by the Financial Services Authority

--
http://www.freelists.org/webpage/oracle-l



text/html attachment: C.htm

Received on Thu Oct 20 2005 - 03:49:45 CDT