How to determine Repeated Records in Oracle database tables

Source: Internet
Author: User

The ORACLE tutorial is: how to determine the repeated records of Oracle database tables. As an Oracle database developer or DBA, you will often encounter this problem in practice: when trying to create a unique index for a column or several columns in the database table, the system prompts the ORA-01452: you cannot create a unique index and duplicate records are found.

The following uses the code_ref table as an example to discuss this problem and its solution.

ERROR is located in row 1st:

ORA-01452: Unable to create unique index; duplicate keyword found

The Oracle system prompts that a unique index cannot be created for the table code_ref, because the system finds that duplicate records exist in the table. You must first locate the duplicate record in the table and delete the record to create a unique index. The following describes three different methods to determine repeated records in the database table.

I. Self-join query method

In Oracle, all tables have a unique column, which is rowid. Using the max or min function for this column can easily determine duplicate rows.

Ii. group by/HAVING Query Method

The group by/HAVING function can easily determine duplicate rows. To create a column group with a unique index and count the number of each group, it is obvious that if the number of records in the group exceeds one, duplicate rows exist.

Iii. Exception into clause

The Exception into clause in the alter table command can also be used to determine duplicate records in the warehouse picking table. This method is a little more troublesome. To use the "upgrade eion into" clause, you must first create the conditions table. The SQL script file used to create the table is utlexcpt. SQL. For NT and UNIX systems, Oracle stores the file in a slightly different location. In the NT System, the script file is stored in the ORACLE_HOME \ Ora81 \ rdbms \ admin directory; for UNIX systems, the script file is stored in the $ ORACLE_HOME/rdbms/admin directory.

ERROR is located in row 2nd:

ORA-02299: unable to verify (SYSTEM. I _CODE_REF)-duplicate keywords not found

The following table associates the exceptions table with the code_ref table through rowid to obtain repeated records in the code_ref table.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.