it is from metalink:Note:465840.1
1>Monitor the temp space allocation to make sure each instance has enough temp space available and that the temp space is allocated evenly among the instances. The following SQL is used:
select inst_id, tablespace_name, segment_file, total_blocks,
used_blocks, free_blocks, max_used_blocks, max_sort_blocks
from gv$sort_segment;
select inst_id, tablespace_name, blocks_cached, blocks_used
from gv$temp_extent_pool;
select inst_id,tablespace_name, blocks_used, blocks_free
from gv$temp_space_header;
select inst_id,free_requests,freed_extents
from gv$sort_segment;
2>If temp space allocation between instances has become imbalanced, it might be necessary to manually drop temporary segments from an instance. The following command is used for this:
alter session set events 'immediate trace name drop_segments level <TS number + 1>';
3〉SQL STATEMENT running on temporary tablespace
Select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as Space,
tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash
and s.address=su.sqladdr
order by se.username,se.sid;
you can kill these sql statement:
alter system kill session 'sid,serial#';
转载：http://blog.itpub.net/543979/viewspace-448444/
