How to find out the temporary space that is required for an SQL query in a database?

Question

we have a client who encountered a problem with some inconsistent data, and we gave them a request to fix it.

Now, before starting a request, clients ask me for a request that will give the temporary space needed to complete this request. This is really important because this query can affect many records in a table.

Here is the request I sent them to fix my 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;

Thank you for your help!

Answer 1

Oracle EXPLAIN PLAN can give you some insight into the cost of a query.