more rows popping up in prod table than the mirror table!

[dparam]

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

[dparam]

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,

[jmodic]

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!

[mber]

Are you talking about composite primary key including 3 keys?

Thanks

[dparam]

I am sorry for the confusion. I mean a composite key based on 3 columns.

[kmesser]

There's a difference in the key values . . . do they contain character or date information? Check for spaces, case and datetime information . . .

[dparam]

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,

[akkerend]

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.

[dparam]

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?

[bkling]

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.