Saturday, September 17, 2011

Script to find duplicates for primary key or Unique Index


This script will help you to find duplicate values coming in any primary key or unique key or unique index fields.
This will be very useful when you encounter any issues during datapump export or import, when a unique index creation fails with "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found"
REM This is an example SQL*Plus Script to delete duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified , you MUST separate with commas.
prompt
accept c prompt 'Column(s): '
prompt
delete from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/

0 comments:

Post a Comment

 

ORA-BLOG. Copyright 2008 All Rights Reserved Revolution Two Church theme by Brian Gardner Converted into Blogger Template by Bloganol dot com