Three methods for querying duplicate records in Oracle

Source: Internet
Author: User

There are many ways to query duplicate records in Oracle. The following describes the three most common methods for querying duplicate records in Oracle.

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.

Explain how to modify the table structure in Oracle

The section of the Oracle database

Three phases of Oracle Startup Mode

Oracle Database Restart Method

Use dynamic SQL for Oracle stored procedures

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.