oracle help

Hi,

In a table there are many columns
When they try to create a index combining all 3 columns (col1,col2,col3) , it failed with duplicate entries in table.
What is the sql to remove those duplicates in oracle 9 database

Thanks
LVL 5
magentoAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
Are you sure you should be deleting records?  I always question something like this.  They say it "should" be unique but it really isn't (and then they find the person that knows why).  There may be a reason for duplicate records.

One thing to do is to get a list of all the duplicate records.  You can do this with an exception table.  You create the table by running the utlexcpt.sql script in $ORACLE_HOME/rdbms/admin.  You should be able to shortcut that by running @?/rdbms/admin/utlexcpt from SQL*Plus on the server.  Once you do that (all it does is create a table), then add EXCEPTIONS INTO EXCEPTIONS to your CREATE INDEX statement.  The ROWIDs of the records that contain duplicates will end up in a table called EXCEPTIONS and you can query the base table from there.  Gets the list easily and makes it easy to pull the records and have someone look at them.
0
 
PortletPaulfreelancerCommented:
Could you provide some sample data and the expected result? If possible also supply the DDL to create this table.
A sample does NOT have to be large but should demonstrate the problem.

In the other columns of that table, is there any field that is unique for every row?

Can you use row_number() in your version of Oracle? e.g. does this query work without error?

select row_number() over(order by 1) as rn
from (select 1 as x from dual union all select 1 from dual)

and produce this expected result:
RN
1
2
0
 
Wasim Akram ShaikCommented:
There are various ways to delete duplicate rows in a table,

as you specified 3 columns in the index, i have placed three columns here.. if there are more.. then specify them accordingly..

also replace DELETE FROM with SELECT * FROM to check and confirm before the rows which you are deleting..

DELETE FROM 
   <your_table_name> A
WHERE 
  a.rowid > 
   ANY (
     SELECT 
        B.rowid
     FROM 
        <your_table_name> B
     WHERE 
        A.col1 = B.col1
     AND 
        A.col2 = B.col2
AND 
A.col3=b.col3
        );

Open in new window


There are many ways to delete duplicates in a table..
check out burleson site for few illustrations

http://www.dba-oracle.com/t_delete_duplicate_table_rows.htm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.