Three methods for querying duplicate records in Oracle

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