Problem solve Get help with specific problems with your technologies, process and projects.

Not able to remove duplicate rows

I am developing software and administering database of CET (Common Entrance Test) Exams in one of the province on India. Hardware and platform I am using are:

  • HP PIV machine as a server (1.6 GHz)
  • 384 MB RAM
  • Windows 2000 Advanced Server
  • Oracle 8i (8.1.7)
Recently I created a database of about 15,000 records. Other than text fields there were three BLOB fields (JPG images), data I loaded was through SQL*Loader. When all the data was loaded in the database (USERS tablespace which was of 800 MB and 566 MB full), I tried to check for duplicate rows in the database, but whenever I tried query (Delete from app_form where rowid not in (select min(rowid) from app_form group by app_form), server got halted and all the clients (6 Clients) also suffered heavily in performance) and I was never able to remove duplicate rows, all clients were PIV machines with 128 MB RAM and no other software was intalled except Oracle 8i. Because of this I am in a big problem, because I am going to use same database in counselling of the same exam, very soon.

How is your SGA configured? In other words, how did you allocate shared_pool_size, db_block_buffers, log_buffer and java_pool_size? You can get the total by typing in SHOW SGA in SQL*Plus while logged in as a DBA user. It sounds like you may need to look into your configuration.

Another thing to consider is that since you used SQL*Loader to bring the data in, you may need to gather statistics so the optimizer has "good" data on which to base execution plan decisions. Use either ANALYZE TABLE or the DBMS_STATS package to collect statistics on all the tables. This may help.

Did you look in v$session_wait when things bogged down to see what exactly was being waited on? This information is needed so you can know exactly what Oracle was doing. Until you know what is going on, you'll have a hard time "guessing" how to fix it.

For More Information

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.