While trying to develop a BPA Workflow using collections, I had repeated issues with the Temp tablespace filling up when debugging.  It seems that whenever an element of the workflow is called, the message data is written to the temp table space.  In my case, the data was around 180k, being written for about 15 actions 150 times.  This doesn’t take long to add up.

Has anyone found a way to get round this, apart from finding and killing the offending session?

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
          P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
          COUNT(*) statements
 FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE    T.session_addr = S.saddr
 AND      S.paddr = P.addr
 AND      T.tablespace = TBS.tablespace_name
 GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
          P.program, TBS.block_size, T.tablespace
 ORDER BY sid_serial;

 ALTER SYSTEM KILL SESSION 'S.sid || ',' || S.serial#';