In 9i, it is simple.

• You can now estimate Sort Area Size (in temp tablespace) needed by a Query that has group by (or order by ) clause.

delete from plan_table ;

explain plan for
select row_id, created_by, conflict_id, x_profile_ind, x_segment, count(*)
from s_org_ext
group by row_id, created_by , conflict_id, x_profile_ind, x_segment ;

select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11M| 222M| | 156K|
| 1 | SORT GROUP BY | | 11M| 222M| 766M| 156K|
| 2 | TABLE ACCESS FULL | S_ORG_EXT | 11M| 222M| | 108K|
----------------------------------------------------------------------------------------------------

From the plan, I come to know that 766M temp space is required.

Tamil