Forum OpenACS Q&A: Response to Oracle temp tablespace is huge!

Posted by Andrew Piskorski on
Oh, also, this might be mildly usefull: While your drop tablespace command seems to be hanging, if you run the query below you should see that Oracle user SYS is executing this SQL: "delete from fet$ where file#=:1 and block#=:2 and ts#=:3".
set linesize 180 
   p.spid  as os_pid 
  ,s.sid  ,s.serial#  
  --,p.username  as os_user 
  ,s.username  ,s.status 
  --,p.terminal  ,p.program 
from   v$session s, v$sqltext sql 
  ,v$process p 
where  sql.address    = s.sql_address 
  and  sql.hash_value = s.sql_hash_value 
  and  p.addr = s.paddr 
  --and p.spid in (4656) 
  and upper(s.username) = 'SYS' 
order by s.username ,s.sid ,s.serial# ,sql.piece ;

And the os_pid will be the PID that top shows sucking up your CPU.