You might be working at work. When you try to create a unique index to a column or column in a library table, you are prompted to ORA-01452: You cannot create a unique index and find duplicate records.
Here's a summary of several ways to find and delete duplicate records (for example, CZ):
The table CZ structure is as follows:
Sql> desc CZ
Name Null? Type
----------------------------------------- -------- ------------------
C1 Number (10)
C10 Number (5)
C20 VARCHAR2 (3)
How to delete duplicate records:
(1). In Oracle, each record has a rowid,rowid that is unique across the database, ROWID determines which data files, blocks, and rows are in Oracle for each record.
(2). In duplicate records, all columns may have the same content, but ROWID will not be the same, so just make sure that those with the largest rowid in the duplicate record are OK, and all the rest is deleted.
The criteria for duplicate record determination are:
C1,C10 and C20 are the same values in each of these three columns to be considered duplicate records.
After the view table CZ total total of 16 records:
Sql>set pagesize 100
Sql>select * from CZ;
1. Several ways to find duplicate records:
(1). Sql>select * from CZ GROUP by C1,C10,C20 have Count (*) >1;
C1 C10 C20
---------- ---------- ---
1 2 DSF
2 3 che
3 4 DFF
(3). Sql>select * from CZ a where rowid= (select Max (ROWID) from CZ where C1=a.c1 and C10=A.C10);
C1 C10 C20
---------- ---------- ---
1 2 DSF
2 3 che
3 4 DFF
2. Several ways to delete duplicate records:
(1). Applies to situations where there is a large number of duplicate records (when indexed on C1,C10 and C20 columns, the following statements are highly efficient):
Sql>delete CZ where (C1,C10,C20) in (select C1,c10,c20 to CZ GROUP by C1,C10,C20 have Count (*) >1) and rowID not In
(select min (rowid) from the CZ group by C1,C10,C20 have Count (*) >1);
Sql>delete CZ where rowID not in (select min (rowid) from CZ Group by C1,C10,C20);
(2). Applies to situations where there is a small number of duplicate records (note that the following statement is inefficient for cases with large amounts of duplicate records):
Sql>delete from CZ A where a.rowid!= (select Max (ROWID) from CZ b where a.c1=b.c1 and A.C10=B.C10);
Sql>delete from CZ A where a.rowid< (select Max (ROWID) from CZ b where a.c1=b.c1 and A.C10=B.C10);
Sql>delete from CZ a where rowID < (select Max (ROWID) from CZ where C1=a.c1 and C10=A.C10);
(3). Applicable to cases with a small number of duplicate records (temporary table method):
Sql>create table Test AS SELECT DISTINCT * from CZ; (Create a temporary table test to hold duplicate records)
Sql>truncate table CZ; (Clears the CZ table's data, but retains the CZ table structure)
Sql>insert into CZ select * from test; (then insert the contents of the temporary table Test back again)
(4). Applicable to situations with a large number of duplicate records (Exception into clause method):
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 Win2000 Systems and UNIX systems, Oracle holds the file in a slightly different location, under the Win2000 system, where the script file resides in the $oracle_home\ora90\rdbms\admin directory, and for UNIX systems, the script file is stored in the $ORACLE the _home/rdbms/admin directory.
The specific steps are as follows:
Sql>@?/rdbms/admin/utlexcpt.sql
Table created.
SQL>DESC exceptions
Name Null? Type
----------------------------------------- -------- --------------
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.