How to find out the temp space that will be required by a sql query on a database?

Question

we have customer who faced an issue with some inconsistent data and we gave them a query to fix it.

Now before running the query, the customers asks me for a query that will give the temp space required for running that query. This is actually important as this query could be affecting a lot of entries in the table.

Here is the query that I sent them to fix their issue:

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;

Thanks for your help!

Answer 1

Oracle's EXPLAIN PLAN might give you some idea of query costs.