查询临时表空间的使用情况：
select * from (select a.tablespace_name,sum(maxbytes/1024/1024/1024) total_G,sum(a.bytes/1024/1024) allocated_mb from dba_temp_files a where a.tablespace_name=upper('XXX') group by a.tablespace_name) x, (select sum(b.bytes_used/1024/1024) used_mb, sum(b.bytes_free/1024/1024) free_mb from v$temp_space_header b where b.tablespace_name=upper('XXX') group by b.tablespace_name) y;
查询现有运行中占据临时表空间的SQL语句：
select se.sid,se.username,su.blocks*ts.block_size/1024/1024 mb_used,su.tablespace, su.sqladdr address,sq.hash_value,sq.sql_text from v$sort_usage su,v$session se,v$sqlarea sq,dba_tablespaces ts where su.session_addr=se.saddr and su.sqladdr=sq.address(+) and su.tablespace=ts.tablespace_name;
查询会话汇总下，临时表空间的使用情况：
select se.sid,se.username,se.osuser,pr.spid,se.module,se.program, sum(su.blocks)ts.block_size/1024/1024 mb_used,su.tablespace, count() sorts from v$sort_usage su,v$session se,dba_tablespaces ts,v$process pr where su.session_addr=se.saddr and se.paddr=pr.addr and su.tablespace=ts.tablespace_name group by se.sid,se.username,se.osuser,pr.spid,se.module,se.program,ts.block_size,su.tablespace;
---注意有时我们看到 unable to extend temp segment by 1024 in tablespace appdata; 不仔细以为是临时表空间不足，其实这里应该看appdata表空间大小
