Methods for determining Repeated Records in Oracle database tables

Oracle DatabaseDevelopers or DBAs often encounter the following problems in their actual work:TableWhen you create a unique index for one or more columns in, the system prompts ORA-01452: you cannot create a unique index and duplicate records are found. Next we will explain in detail how to determine Repeated Records in Oracle database tables.

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.

Through the above study, I believe that everyone has mastered the method of determining repeated records in the Oracle database table. This technique will be very useful in your future work and hope to help you.