we've customer who faced an problem with a few sporadic data and that we gave them a question to repair it.

Now before you run the query, the clients asks me for any query which will provide the temp space needed for running that question. This really is really essential as this question might be affecting lots of records within the table.

This is actually the query which i sent these to fix their problem:

declare
  cursor cur is select distinct SEQID from D_LEAD where SEQID IN( SELECT SEQID FROM D_LEAD WHERE CURR_FLAG = 1 GROUP BY
  SEQID HAVING COUNT(SEQID) >1);

  seq NUMBER; 

begin

  open cur;
  loop
    fetch cur into seq;
    update D_LEAD set CURR_FLAG = 0 where LEAD_ID IN (SELECT LEAD_ID FROM D_LEAD WHERE ((LEAD_ID != (SELECT MAX(LEAD_ID) FROM D_LEAD WHERE SEQID=seq)) AND SEQID=seq));
    exit when cur%NOTFOUND;
  end loop;
  close cur;
commit;
end;

Interesting help!

Oracle's EXPLAIN PLAN might provide you with an idea of query costs.