临时表空间管理有时也是很棘手的问题，做下笔记以备后事所需。

查看当前临时表空间使用率：

点击(此处)折叠或打开

select * from (select a.tablespace_name,sum(a.bytes/1024/1024)allocated_mb
from dba_temp_files a where a.tablespace_name = upper('&&temp_tsname')
group by a.tablespace_name) x,(selec sum(b.bytes_used/1024/1024) free_mb,sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b where b.tablespace_name=upper('&&temp_tsname')
group by b.tablespace_name);

如果发现临时表空间快满了，这就要找出产生高临时表空间使用率的原因。

查询出那条sql语句将排序数据段中的空间用光了：

点击(此处)折叠或打开

select s.sid ||','|| s.serial# sid_serial,s.username,
o.blocks * t.block_size/1024/1024 mb_used,o.tablespace,
o.sqladdr address,h.hash_value,h.sql_text
from v$sort_usage o,v$session s,v$sqlarea h,dba_tablespaces t
where o.session_addr = s.saddr and o.sqladdr = h.address (+)
and o.tablespace = t.tablespace_name order by s.sid;

oracle尽量在内存PGA中进行排序和散列运算，但是如果一个排序太大，内存中无法容纳，就会适应临时表空间进行。需要理解一点是，即使只有一个很大的排序运算，也有可能将整个临时表空间耗尽。因为所有数据会话都共享临时表空间，运行一个很大的排序运算会话就会有可能导致其他所有会话产生空间不足的问题，一旦临时表空间被占满，所有试图使用临时表空间的sql语句就汇报ORA_1652：UNABLE TO EXTEND TEMP SEGMENT（这个后文会讲到如何处理）。新的会话可能无法连接上。查询可能会被挂起，用户也可能不能在提交新的查询，这时如果试着去找出可能产生阻塞的锁，就会发现并没有任何阻塞锁存在。如果临时表空间被占满，事务就无法完成。
使用order by 或者group by 子句的运算，频繁使用临时段来完成工作，当然，创建索引或者重建索引，也是会需要临时表空间来进行排序的。
Oracle使用PGA内存来进行排序和散列运算，因此，首先要做的事情就是查看当前为PGA_AGGREGATE_TARGET初始化参数所设置的值，并且看看将其提高是否有帮助。但是，即使设置了PGA_AGGREGATE_TARGET参数设置了更大的值，也并不能保证Oracle会完全在内存中来进行大的排序运算。
如果数据库中的临时表空间用完了，则必须增加一个临时文件来增加其大小。启用临时表空间空间的自动扩展也会防止“空间不足”问题，因为Oracle会在临时表空间中分配一块空间，来进行排序运算，也可以专门为进行大量排序的用户分配一个临时表空间，防止这些大的排序损害了数据库性能。

注意，不同于表数据段或者索引段对于每个对象可能有多个，但是临时表空间只有一个段，及排序段。所有会话共享这个排序段，一条sql语句可能会使用多个排序段和散列运算。此外，同一个会话可能有多个sql语句同时运行，每个语句都有可能使用多个排序和散列运算。一个排序运算完成后，数据库就会马上将该运算所使用的数据块标记为空闲，并将它们分配给另一个排序运算。

ITPub博客

【优化思路集】临时表空间问题

最新文章