Methods to determine duplicate records in an Oracle database table

As an Oracle Database developer or DBA, you often experience the problem of trying to create a unique index on a column or column in a library table, prompting ORA-01452: You cannot create a unique index and find duplicate records.

Here we take table code_ref as an example to discuss this problem and its solution.

Error is on line 1th:

ORA-01452: Unable to CREATE UNIQUE INDEX; Find duplicate keyword

Oracle System hints The table Code_ref cannot be created with a unique index because duplicate records exist in the System Discovery table. We must first find the duplicate records in the table and delete the record before we can create a unique index. Here are three different ways to determine duplicate records in a library table.

I. Self-associated Query method

In an Oracle system, there is a unique column for all tables, and this is rowid. Using the maximum (max) or minimum (min) function for the column makes it easy to determine the duplicate rows.

Second, GROUP by/having Query method

It is also easy to identify duplicate rows by using group by/having of grouping functions. Group the columns that need to create a unique index and count the number of each group, obviously if there are more than 1 records in the group there are duplicate rows.

Three, Exception into clause

The exception into clause in the ALTER TABLE command can also be used to determine duplicate records in a library table. This approach is somewhat cumbersome, and in order to use the "excepeion into" clause, you must first create a exceptions table. The SQL script file that created the table is utlexcpt.sql. For NT systems and UNIX systems, ORACLE holds the file in a slightly different location, under the NT system, the script file is stored in the Oracle_home\ora81\rdbms\admin directory, and for UNIX systems, the script file is stored in the $oracle_ The Home/rdbms/admin directory.

Error is on line 2nd:

ORA-02299: Cannot verify (system.i_code_ref)-Duplicate keyword not found

The following exceptions tables and CODE_REF tables are associated with ROWID to get duplicate records in the table Code_ref.