Getting duplicate records

pts.
Tags:
Oracle 9i
I have looked onto the web for finding duplicate records. However most the queries given finds one of the duplicate records. Our requirement is to find both (or multiple) duplicate records from a table. We are using Oracle 9i. Regards

Answer Wiki

Thanks. We'll let you know when a new response is added.

See my reply in subject “Duplicate records”.

Regards,
ActorJack

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Pegasusdba
    First, identify what constitutes a "duplicate". This is paraphrased from http://www.sap-img.com/oracle-database/how-do-you-tell-what-rows-are-duplicates-of-each-other.htm The quickest way to find duplicates is to stick a UNIQUE constraint on the relevant columns, and attempt to enable that constraint. First, create the constraint definition: ALTER TABLE BLAH ADD (CONSTRAINT EMPID_UQ UNIQUE DISABLED NOVALIDATE); Now we create a special table to house the errors that arise if we attempt to enforce the constraint (i.e., switch it fully on). Oracle supplies a script to create that table (it's called utlexcpt.sql, and it's found in the ORACLE_HOME/rdbms/admin directory). The next thing to do is try and enable the unique constraint, but specifying that any errors arising from doing so should be written into this new exceptions table we've just created. Try this: ALTER TABLE BLAH ENABLE CONSTRAINT EMPID_UQ EXCEPTIONS INTO EXCEPTIONS; The enabling of the constraint failed, but the trick is that the exceptions table now contains details of what rows exist that are duplicates, and thus prevent the constraint being fully enabled. All we need to do now is select * from exceptions, and we shall be able to see the offending records: ROW_ID OWNER TABLE_NAME AAAH2NAABAAAPCMAAA SCOTT BLAH AAAH2NAABAAAPCMAAB SCOTT BLAH Now join on the rowid of the exception column to the "BLAH" table rowid: SELECT * FROM BLAH WHERE ROWID IN (SELECT ROW_ID FROM EXCEPTIONS); And now we get a report that looks like this: EMPNO NAME 1 FRED 1 FREDERIKA ...and it becomes obvious where the problem is.
    0 pointsBadges:
    report
  • Brijesh
    Hi,

    Check the resource How to find duplicate values in a table hope this helps.
    10,185 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following