From oracle-l-bounce@freelists.org Wed Oct 19 18:23:48 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9JNNcr0011342 for ; Wed, 19 Oct 2005 18:23:43 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9JNNQvX011316 for ; Wed, 19 Oct 2005 18:23:31 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2BEB7208451; Wed, 19 Oct 2005 18:23:15 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 14521-10; Wed, 19 Oct 2005 18:23:15 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7066C208340; Wed, 19 Oct 2005 18:23:14 -0500 (EST) Message-ID: <062c01c5d503$c9f8a160$61f5a8c0@TerrySutton> From: "Terry Sutton" To: References: <20051019175811.44385.qmail@web60712.mail.yahoo.com> Subject: Re: ** anyway to create a primary key on table with duplicates that cannot be deleted Date: Wed, 19 Oct 2005 16:16:04 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_05DB_01C5D4C8.67424070" x-mimeole: Produced By Microsoft MimeOLE V6.00.2800.1441 X-ELNK-Trace: 5830af7b3b91928f1aa676d7e74259b7b3291a7d08dfec79c7158e2042794a550a3dbca86aa4dc84350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 4.20.98.115 X-archive-position: 27229 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: terrysutton@usa.net Precedence: normal Reply-To: terrysutton@usa.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-3.2 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE autolearn=ham version=2.63 ------=_NextPart_000_05DB_01C5D4C8.67424070 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable A, 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..=20 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. ------=_NextPart_000_05DB_01C5D4C8.67424070 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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

In a session on each = database

1. exec=20 dbms_reputil.replication_off

2. Delete the duplicates using any one = of the many=20 queries people use to do this (you may want to create a non-unique = index on=20 what should be the primary key column before you do this)

3. Create the primary key (after = dropping the=20 non-unique index)

4. exec = dbms_reputil.replication_on

--Terry

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

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

------=_NextPart_000_05DB_01C5D4C8.67424070-- -- http://www.freelists.org/webpage/oracle-l