Temp表空间使用问题

hwqhx

用以下语句查询当前临时表空间使用大小
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;

发现使用总额１.１G，但是实际临时表空间大小有上百G，但是这时候系统会报ｔｅｍｐ不足。

请问为何？

Maclean Liu(刘相兵

请运行以下SQL 并上传输出:

1. set linesize 200
   
2. select h.tablespace_name,
   
3.        round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
   
4.        round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
   
5.              1048576) megs_free,
   
6.        round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
   
7.        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
   
8.              sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
   
9.        100 -
   
10.        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
    
11.              sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
    
12.        round(sum(f.maxbytes) / 1048576) max
    
13.   from sys.v_$TEMP_SPACE_HEADER h,
    
14.        sys.v_$Temp_extent_pool  p,
    
15.        dba_temp_files           f
    
16. where p.file_id(+) = h.file_id
    
17.    and p.tablespace_name(+) = h.tablespace_name
    
18.    and f.file_id = h.file_id
    
19.    and f.tablespace_name = h.tablespace_name
    
20. group by h.tablespace_name
    
21. ORDER BY 1
    
22. /

复制代码

gtlions

数据文件是否自动扩张？数据文件最大大小多少？

gtlions

以前也有遇到过，临时表空间的数据文件是很小的，并且自动扩张的，磁盘剩下足够大，但是在执行一个存储过程的时候，在最后都要报错临时表空间无法扩张，过程中执行了排序、分组等等操作，后来通过resize temp文件到一个固定值10g后解决了，当时也说清楚原因是为什么，现在想想似乎是由于临时表空间在next的时候出现的问题

hwqhx

语句结果请见附件

Maclean Liu(刘相兵

1.
就输出来看TEMP 空闲率为99% ， 可能因为sort hash已经过去

2.
from v$sort_usage sort, v$session sess, v$sql sql

你的查询未必有价值， 因为和 v$sql 和v$session关联了 但这2个视图中未必就有相关的记录

若仍发生问题 直接查询

select * from v$sort_segment;
select * from v$sort_usage;


3. 你可以利用 http://www.oracledatabase12g.com ... -sort-activity.html 这个脚本了解之前实例中 是否出现过大的sort， 依赖于动态视图 信息可能不完整

Maclean Liu(刘相兵

再说一次 没事就截图是很坏的习惯 除非是GUI界面！

hwqhx

如果之前出现大的排序，那现在应该释放了吧，为啥现在系统还报temp不足？

Maclean Liu(刘相兵

你指的报 temp不足是如何表现的？

出现在告警日志中？ 请把 信息贴出来， 同时把时间线 理清楚

hwqhx

故障情况：
发现alert报错：
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
然后查临时表空间当前使用的量发现总额几百兆，但是表空间还有上百G

Maclean Liu(刘相兵

troubleshooting 请把时间线里清楚

你说"
如果之前出现大的排序，那现在应该释放了吧，为啥现在系统还报temp不足？"

就是说alert 中不断地出现ORA-1652:

请把alert 日志的内容上传

tail -5000 alert*.log