Re: phantom records?
- From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- To: SUzzell@xxxxxxxxxx
- Date: Wed, 10 Nov 2010 15:38:21 +0100
You can try disabling the constraint, load your "duplicate" row, and then
enable the constraint again using the EXCEPTIONS INTO clause to get the
ROWIDs of the offending row(s).
Maybe that'll shed some light on it.
Stefan
=========================
Stefan P Knecht
CEO & Founder
s@xxxxxxxx
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland
Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info@xxxxxxxx
http://www.10046.ch
=========================
On Wed, Nov 10, 2010 at 3:30 PM, Uzzell, Stephan <SUzzell@xxxxxxxxxx> wrote:
> Hi listers, I’ve got an interesting one today:
>
>
>
> SQL> select table_name, constraint_name, constraint_type from
> user_constraints where constraint_name = 'GDS_HOSTS_PK';
>
>
>
> TABLE_NAME CONSTRAINT_NAME C
>
> ------------------------------ ------------------------------ -
>
> GDS_HOSTS GDS_HOSTS_PK P
>
>
>
> SQL> select * from user_cons_columns where constraint_name =
> 'GDS_HOSTS_PK';
>
>
>
> OWNER CONSTRAINT_NAME
>
> ------------------------------ ------------------------------
>
> TABLE_NAME
>
> ------------------------------
>
> COLUMN_NAME
>
>
> -------------------------------------------------------------------------------
>
>
>
> POSITION
>
> ----------
>
> ORS GDS_HOSTS_PK
>
> GDS_HOSTS
>
> GDS_HOST
>
> 1
>
>
>
> Hope that makes it clear – we have a PK on one column in that table.
>
>
>
> SQL> select gds_host from gds_hosts;
>
>
>
> GDS_HOST
>
> --------------------
>
> WB
>
> FIDELIO
>
> MF
>
> TW
>
> NETBOOKER
>
> HD
>
> DHRCOM
>
> EXPEDIA
>
> DCJONV
>
> DCAP
>
> MOBILE
>
>
>
> 11 rows selected.
>
>
>
> Pretty clear what we have in there…
>
>
>
> SQL> insert into GDS_HOSTS
>
> 2 select
>
> 3 'AA',
>
> 4 'SABRE',
>
> 5 CHANNEL_TYPE,
>
> 6 RATE_TYPE,
>
> 7 MULTI_CURRENCY_YN,
>
> 8 SESSION_CONTROL,
>
> <snip>
>
> 75 ;
>
> insert into GDS_HOSTS
>
> *
>
> ERROR at line 1:
>
> ORA-00001: unique constraint (ORS.GDS_HOSTS_PK) violated
>
>
>
> But do you see ‘AA’ as a gds_host in there?
>
>
>
> And just to prove it is not a SQL syntax issue – same insert, I just
> changed line 3 from ‘AA’ to ‘AA1’:
>
>
>
> SQL> insert into GDS_HOSTS
>
> 2 select
>
> 3 'AA1',
>
> 4 'SABRE',
>
> 5 CHANNEL_TYPE,
>
> 6 RATE_TYPE,
>
> 7 MULTI_CURRENCY_YN,
>
> 8 SESSION_CONTROL,
>
> <snip>
>
> 75 ;
>
>
>
> 1 row created.
>
>
>
> SQL> rollback;
>
>
>
> Rollback complete.
>
>
>
>
>
> I’ve dropped and recreated the index and the constraint, analyzed the
> table, but still getting the same oddness. Any suggestions?
>
>
>
> *
> _____________________________________________________________________________
> *
>
> *Stephan Uzzell |** **MICROS Systems, Inc.** *
>
>
>
> Database Administrator - OPERA Global Technical Services
>
> 7031 Columbia Gateway Dr, Columbia, MD 21046 | ( 443.285.8000x2760 |
> 7443.285.6505
>
>
>
Other related posts:
