Estimate TEMP usage without running SQL in Oracle

I was running a SQL with huge sorting but each time job failed because it was not able to extend TEMP tablespace.As the result, I needed to know how to determine required TEMP usage without running SQL statement.

My immediate answer was :
- Login to database with the same user.
- Get Execution plan.
- Watch TempSpc column in execution plan.

Client called me back and said "there is not TempSpc column in execution plan."(By the way client runs latest 10g version (10.2.0.3)).

There are basically two reasons that TempSpc may not appear in execution plan.

Reason 1 : SQL statement does not need temporary tablespace and dedicated memory (PGA) is enough to run all operations

Reason 2 : Plan table is old and has not any column to keep Temp space usage.

To update plan, run the followings :
- drop table plan_table
- @$ORACLE_HOME/rdbms/admin/utlxplan.sql

(If you are using sqltrace for getting execution plan, you need to run @$ORACLE_HOME/sqlplus/admin/plustrce.sql as sys and then grant PLUSTRACE to the user)