I have found the query whose Hash Join is asking for more than 16GB of temp tablespace. Here is the query in with the explain plan. The tables are all analyzed everyday and I am running Oracle 9.2.0.6 on Linux.
Code:
SELECT COUNT(DISTINCT car.assessment_locator) AS assessments_mapped 
FROM      CLG_ASSESSMENT_RESULT        car,      CLG_ITEM_RESULT              cir,      CLG_ITEM                     ci,      CLG_CORRELATION              corr,      CB_STANDARD                  cbs,   CLG_ASSESSMENT_INSTANCE cai,   CLG_USER u  
WHERE        u.org_unit_id = :1        
AND u.user_id = cai.assignor_id        
AND car.ASSESSMENT_locator = :2       
AND car.completed_date >= :3        
AND car.completed_date <= :4        
AND cir.ASSESSMENT_RESULT_ID = car.ASSESSMENT_RESULT_ID        
AND (ci.LOCATOR = corr.CONTENT_ID OR ci.ancestor_locator = corr.content_id)        
AND corr.ASSET_TYPE_ID = 113        
AND corr.cbs_id = cbs.CBS_ID       
AND ci.LOCATOR = cir.item_locator


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=120 Card=1 Bytes=208 )
   1    0   SORT (GROUP BY)
   2    1     HASH JOIN (Cost=120 Card=6846 Bytes=1423968)
   3    2       MERGE JOIN (CARTESIAN) (Cost=107 Card=320 Bytes=64960)
   4    3         NESTED LOOPS (Cost=65 Card=1 Bytes=193)
   5    4           NESTED LOOPS (Cost=64 Card=1 Bytes=189)
   6    5             NESTED LOOPS (Cost=15 Card=1 Bytes=154)
   7    6               NESTED LOOPS (Cost=13 Card=1 Bytes=88)
   8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ASSESSMENT_RESULT' (Cost=10 Card=1 Bytes=45)
   9    8                   BITMAP CONVERSION (TO ROWIDS)
  10    9                     BITMAP AND
  11   10                       BITMAP CONVERSION (FROM ROWIDS)
  12   11                         SORT (ORDER BY)
  13   12                           INDEX (RANGE SCAN) OF 'IDX_ASMNTRES_COMPDATE' (NON-UNIQUE) (Cost=2 Card=184)
  14   10                       BITMAP CONVERSION (FROM ROWIDS)
  15   14                         INDEX (RANGE SCAN) OF 'IDX_ASMNTRES_ASMNTLOC' (NON-UNIQUE) (Cost=4 Card=184)

  16    7                 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ITEM_RESULT' (Cost=3 Card=12 Bytes=516)

  17   16                   INDEX (RANGE SCAN) OF 'PK_ITEM_RESULT' (UNIQUE) (Cost=2 Card=12)

  18    6               TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ITEM' (Cost=2 Card=1 Bytes=66)

  19   18                 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1 Card=1)

  20    5             TABLE ACCESS (BY INDEX ROWID) OF 'CLG_CORRELATION' (Cost=64 Card=1 Bytes=35)

  21   20               BITMAP CONVERSION (TO ROWIDS)
  22   21                 BITMAP OR
  23   22                   BITMAP CONVERSION (FROM ROWIDS)
  24   23                     SORT (ORDER BY)
  25   24                       INDEX (RANGE SCAN) OF 'PK_CORRELATION' (UNIQUE) (Cost=2 Card=32064)

  26   22                   BITMAP CONVERSION (FROM ROWIDS)
  27   26                     SORT (ORDER BY)
  28   27                       INDEX (RANGE SCAN) OF 'PK_CORRELATION' (UNIQUE) (Cost=2 Card=32064)

  29    4           INDEX (UNIQUE SCAN) OF 'PK_CB_STANDARD' (UNIQUE)
  30    3         BUFFER (SORT) (Cost=107 Card=435 Bytes=4350)
  31   30           TABLE ACCESS (BY INDEX ROWID) OF 'CLG_USER' (Cost=42 Card=435 Bytes=4350)

  32   31             INDEX (RANGE SCAN) OF 'IDX_USER_ORGUNITID' (NON-UNIQUE) (Cost=2 Card=435)

  33    2       INDEX (FAST FULL SCAN) OF 'IDX_ASMNTINST_ASSIGNORID' (NON-UNIQUE) (Cost=9 Card=35353 Bytes=176765)