Oracle Scratchpad

June 4, 2010

Quiz Night

Filed under: Troubleshooting — Jonathan Lewis @ 7:48 pm BST Jun 4,2010

A recent question on OTN gave the following information:

I’m having problem with my database (Oracle on Solaris 9), which contains more than 1 row with a same value on a field that has uniqueness constraint.

Could you please help tell me how to fix this?

Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it. And it is still in VALID state

SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574);

        RI SCNUM
---------- ----------
 536955574 444393975
 536964983 444393975

So we have a unique index on the single column SCNUM, but we can report two rows with the same value for SCNUM.

Later in the thread there was a report that suggested that the index was probably corrupted – but there is an important clue in output above which tells you that the index could be unique, valid, and not corrupt and produce the results above. Can you see what that clue is ?

Someone in the thread spotted the clue – so don’t look at the thread until you’ve given yourself a minute to work out the answer. (I’ve also disabled comments to stop anyone posting the answer where others can see it before they’ve had a chance to work it out for themselves.)

Update 25th June: The clue is in the headings – “RI” has been right-aligned, and “SCNUM” has been left-aligned. This means (assuming default behaviour) that RI is a numeric column but SCNUM is, despite its name, a character column. It’s possible that there’s an invisible space at the end of one of the two “identical” values. A quick check would be to change the  query to select dump(scnum) rather than scnum.


  1. I had forgotten that I hadn’t posted an update to this quiz, or re-opened it for comments. Now open.

    Comment by Jonathan Lewis — June 26, 2010 @ 9:34 am BST Jun 26,2010 | Reply

  2. The output of SCNUM column is left aligned. This indicates it is
    varchar and not number (Though the output is number). Also at right-most
    side there is scope for one more character. I guess the width is defined as
    10 and data is having width of 9.
    So there must be a space at end of the value for any one row which is
    causing this. We can check space uisng select ri, ”” || scnum || ”” from
    scratch1_p where ri in (536964983, 536955574);

    Comment by Sandeep Redkar — June 27, 2010 @ 5:58 pm BST Jun 27,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by