How do I determine duplicate records for Oracle database tables
- 2020-05-17 06:50:01
- OfStack
The ORACLE tutorial you are looking at is: how to determine duplicate records for Oracle database tables. As an Oracle database developer or DBA, we often encounter this problem in practice: when we try to create a unique index on a column or columns in a library table, we are told ORA-01452: we cannot create a unique index and find duplicate records.
Let's take table code_ref as an example to discuss this problem and its solution.
ERROR is in line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; Find duplicate keywords
The Oracle system prompts that a unique index cannot be created on the table code_ref because the system found duplicate records in the table. We must first find the duplicate record in the table and drop it before we can create a unique index. Here are three different ways to determine duplicate records in a library table.
1. Autocorrelation query method
In the Oracle system, there is one unique column for all tables, which is rowid. Using the maximum (max) or minimum (min) functions on this column makes it very easy to determine duplicate rows.
2. GROUP BY/HAVING query method
It is also easy to identify duplicate rows using the grouping function GROUP BY/HAVING. Group the columns that need to create a unique index and count the number of rows in each group. It is clear that there are duplicate rows if there are more than one records in the group.
3. Exception into clause
Duplicate records in the outbound table can also be determined using the Exception into clause in the alter table command. This is a little trickier. In order to use the "excepeion into" clause, you must first create the EXCEPTIONS table. The SQL script file to create the table is utlexcpt.sql. For the NT system and the UNIX system, the Oracle file is stored in a slightly different location. Under the NT system, the script file is stored in the ORACLE_HOME\Ora81\rdbms\admin directory. For the UNIX system, the script file is stored in the $ORACLE_HOME/rdbms/admin directory.
ERROR is on line 2:
ORA-02299: cannot verify (SYSTEM.I_CODE_REF) - duplicate keyword not found
The following table associates the exceptions table with the code_ref table through rowid to obtain duplicate records in the table code_ref.
Let's take table code_ref as an example to discuss this problem and its solution.
ERROR is in line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; Find duplicate keywords
The Oracle system prompts that a unique index cannot be created on the table code_ref because the system found duplicate records in the table. We must first find the duplicate record in the table and drop it before we can create a unique index. Here are three different ways to determine duplicate records in a library table.
1. Autocorrelation query method
In the Oracle system, there is one unique column for all tables, which is rowid. Using the maximum (max) or minimum (min) functions on this column makes it very easy to determine duplicate rows.
2. GROUP BY/HAVING query method
It is also easy to identify duplicate rows using the grouping function GROUP BY/HAVING. Group the columns that need to create a unique index and count the number of rows in each group. It is clear that there are duplicate rows if there are more than one records in the group.
3. Exception into clause
Duplicate records in the outbound table can also be determined using the Exception into clause in the alter table command. This is a little trickier. In order to use the "excepeion into" clause, you must first create the EXCEPTIONS table. The SQL script file to create the table is utlexcpt.sql. For the NT system and the UNIX system, the Oracle file is stored in a slightly different location. Under the NT system, the script file is stored in the ORACLE_HOME\Ora81\rdbms\admin directory. For the UNIX system, the script file is stored in the $ORACLE_HOME/rdbms/admin directory.
ERROR is on line 2:
ORA-02299: cannot verify (SYSTEM.I_CODE_REF) - duplicate keyword not found
The following table associates the exceptions table with the code_ref table through rowid to obtain duplicate records in the table code_ref.