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#';