How to determine Repeated Records in Oracle database tables

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.