oracle help


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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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:
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..

   <your_table_name> A
  a.rowid > 
   ANY (
        <your_table_name> B
        A.col1 = B.col1
        A.col2 = B.col2

Open in new window

There are many ways to delete duplicates in a table..
check out burleson site for few illustrations
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.