I have a procedure and a particular query in the procedure is generating about 50GB of temp space causing below exception after few executions.: SQL state ; error code ; ORA-01652: unable to extend temp segment by 128 in tablespace TEMP DBAs are pointing to the below query in the stored procedure that needs to be rewritten. The tables used in the query below are smaller than 0.1 GB but the query generates 50GB of temp space! SELECT tab1.ID ID1, tab2.ID ID2 FROM ( SELECT OT.ID, CONNECT_BY_ROOT OT.UNIQ_ORIG_KEY ORIG_UID FROM order_tab OT, status_tab ST WHERE OT.otype IN ('A','B') AND OT.order_uid IS NULL AND OT.BATCH_ID = ST.BATCH_ID AND ST.CT_DATE = :A1 AND ST.BSTATUS = 1 CONNECT BY PRIOR OT.UNIQ_KEY = OT.UNIQ_ORIG_KEY ) tab1 , order_tab tab2 WHERE tab2.ORD_VERID = 1 AND tab1.ORIG_UID = tab2.UNIQ_KEY ORDER BY ID1; Could someone please help in rewriting the query efficiently so that temp space utilization is reduced. Database used Oracle 12c.