problem with duplicates

[WaelYassin]

Dear All, i am using oracle 9i.
i have a table that indexed in workorder# to be unique across the table, we have a program that take the data from the user and stores it in the database.
the program prepare a batch of records and then begin transaction and pass it to the database.
the problem arise when we found duplicates in the database, and we don't know how oracle permits to store them.
any suggestions????

 

[SantaMufasa]

Wael,

Please run this script and post its results for the table that is the subject of your inquiry:

set linesize 200
col x format a30
col table_name like x
col index_name like x
col column_name like x
break on table_name on index_name on uniqueness
select i.table_name,i.index_name,i.uniqueness,c.column_position,c.column_name
from user_indexes i, user_ind_columns c
where i.table_name = c.table_name
  and i.index_name = c.index_name
order by table_name,index_name,uniqueness
/

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[WaelYassin]

TABLE_NAME INDEX_NAME UNIQUENES COLUMN_POSITION COLUMN_NAME
------------------------------ ------------------------------ --------- --------------- ------------
GMS_WORKORD GMS_WORKORD UNIQUE 1 WORKORD_CODE
2 WORKORD_EQUIP_SITE
3 WORKORD_EQUIP_UNIT
4 WORKORD_EQUIP_SECTION
5 WORKORD_EQUIP_CATEGORY
6 WORKORD_EQUIP_CODE
7 APPTABLEINDEX
GMS_WORKORD_DN0 UNIQUE 1 WORKORD_CODE
GMS_WORKORD_DN53 UNIQUE 1 WORKORD_EQUIP_SITE
2 WORKORD_EQUIP_UNIT
3 WORKORD_EQUIP_SECTION

TABLE_NAME INDEX_NAME UNIQUENES COLUMN_POSITION COLUMN_NAME
------------------------------ ------------------------------ --------- --------------- ------------
GMS_WORKORD GMS_WORKORD_DN53 UNIQUE 4 WORKORD_EQUIP_CATEGORY
5 WORKORD_EQUIP_CODE
6 WORKORD_WEEK
7 WORKORD_MAINCATEG
8 WORKORD_JOBTYPE
9 WORKORD_SCHEDULE
GMS_WORKORD_DY0 NONUNIQUE 1 WORKORD_WOSTATUS

18 rows selected.

THAT IS ALL, NOTE THAT THE FIELD THAT MAKE DUPLICATE IS THE WORKORD_CODE.

 

[Dagon]

It's hard to tell from the formatting of your reply, but it looks as if you have a unique index on workord_code:

GMS_WORKORD_DN0 UNIQUE 1 WORKORD_CODE

Therefore, it would be impossible to insert duplicate workord_codes. One possibility is that some workord_codes have spaces on the end, so they may look the same as another value, but the spaces make them different.

Do something like this:

select workord_code, count(*)
from GMS_WORKORD   
group by workord_code
having count(*) > 1

If it is indeed a unique index, this will return no rows.

Now do:

select trim(workord_code), count(*)
from GMS_WORKORD   
group by trim(workord_code)
having count(*) > 1

If the difference is trailing spaces, you should get some rows this time.

 

[WaelYassin]

is there a possibility for oracle to store duplicates if the there is a transaction being processed or an abnormal process run?????
thanks

 

[Dagon]

No, I can categorically say that if you have a unique index on the column, you will not get duplicates. If you are seeing what you think are duplicates, they are not really duplicates. There will be some difference between them (e.g. capitalization, spacing).