-
I am experiencing a problem with 2 tables with same structure , one is production and one is a resolve table, which is mirror of the prod table. Both tables gets populated every day thrice thro sql*loader, with accurate freequency. Now if i query the prod table and resolve table for a specific criteria, prod table comes out with 5 rows more than that of resolve table. It's happening first time after a proper function of nearly an year.
Both tables are having 3 primary keys. How and where to trroubleshoot? Pl help.
Pl give me a SQL statement to check with just duplicate rows present in tables.
Thanx
-
I did find that there are 5 duplicate rows are in the prod table. I am wondering to know how the duplicates are allowed by breaking the primary key constraint. Any help would be appreciated.
Thanx,
-
Well, for the start you could clarify what do you mean by "Both tables are having 3 primary keys". No table in SQL world could ever have more than one primary key!
-
Are you talking about composite primary key including 3 keys?
Thanks
-
I am sorry for the confusion. I mean a composite key based on 3 columns.
-
There's a difference in the key values . . . do they contain character or date information? Check for spaces, case and datetime information . . .
-
There are 2 number colns and one date column.
SWITCHID,SEQUENCENUMBER-----number cols
CALLSTARTTIME---------------------date coln,
Pl let me know what to check for case,space etc., i.e., with the entered values or key names?
Thanx,
-
To find duplicates try:
SELECT SWITCHID, SEQUENCENUMBER, CALLSTARTTIME, count(*)
FROM your_table
HAVING count(*) > 1
GROUP BY SWITCHID, SEQUENCENUMBER, CALLSTARTTIME;
If you select a date column in SQL*Plus without any format , Oracle uses the nls_date_format. Default this is without hours, minutes and seconds.
-
Thanx, I found that there are nearly more than 100 duplicate entries now. It's amazing how composite key violation happening, since the status of the index is USABLE(it's a partitioned index). Any ideas?
-
Was the primary key enabled with novailidate.
If this is the case the table ignores duplicated data and Oracle assumes you want it this way.
-
when i select validated from dba_constraints it says validated, but i didn't find a way to check whether enabled or not?. pl let me know where to check for enabled?.
One more hint is this error started from after upgrading from 8.0.4 to 8.1.7.. when we query uniqueness it says nonunique!,
is it possible for any composite primary key to be nonunique?
-
Hi,
If date is a part of primary key then such problem may occur
if data is not inserted in proper format.
see the below example
SQL> create table t2
2 (
3 id number,
4 idate date,
5 value number);
Table created.
SQL> alter table t2 add primary key(id, idate);
Table altered.
now insert some data in table
SQL> insert into t2 values (1, sysdate, 100);
1 row created.
now again insert same data
SQL> insert into t2 values (1, sysdate, 100);
1 row created.
nmmm !!!!!!
the problem is sysdate & date type column in table . values of sysdate has been changed in next insert by some seconds
so thats why it is getting inserted.
now insert following way
SQL> insert into t2 values (1, to_char(sysdate,'dd-mon-yyyy'),100);
1 row created.
SQL> insert into t2 values (1, to_char(sysdate,'dd-mon-yyyy'),100);
insert into t2 values (1, to_char(sysdate,'dd-mon-yyyy'),100)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C001873) violated
now your getting error.
So, check if there is any such reason.
hope this will help.
Thanks
P. Soni
[Edited by PSoni on 03-20-2001 at 08:00 AM]
