临时表空间不足问题排查步骤****

【问题现象】反洗钱系统在缓存账户信息的时候会因为临时表空间不足导致账户信息缓存失败；

报错信息如上图：unable to extend temp segment by 128 in tablespace

 

【问题分析】报错信息提示临时表空间不足

方法一:首先可以查询临时表空间的占用率

登录sysdba权限的用户查询

查询临时表空间的使用情况，需要借助两个系统视图。Dba_temp_diles，用于存储临时表空间的文件信息，如下图所示临时表空间使用到了两个数据文件。

另一个视图是v$temp_extend_pool

用于记录临时表空间的存储空间信息

select c.tablespace_name,

to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,

to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,

to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,

to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use

from (select tablespace_name,sum(bytes) bytes

from dba_temp_files GROUP by tablespace_name) c,

(select tablespace_name,sum(bytes_cached) bytes_used

from v$temp_extent_pool GROUP by tablespace_name) d

where c.tablespace_name = d.tablespace_name;

如上图所示，use列为临时表空间的占用率。

 

【问题解决】如果查到临时表空间的占用率过高，可临时增加临时表空间的数据文件解决。

1）对于单台数据库，使用如下方法添加

alter tablespace tablespace_name add tempfile ‘/u01/app/oracle/oradata/AMLDB/temp02.dbf’ size 32767M autoextend on;

2）对于RAC数据库，使用如下语句添加：

alter tablespace tablespace_name add tempfile ‘+DATADG’ size 32767M autoextend on;

以上语句中的+DATADG为数据文件的路径，此路径需与DBA确认。

 

以上提到的解决方法治标不治本，要彻底解决需要找到引起占用临时表空间较多的sql，

对sql进行优化。需要使用dba权限查询sql的执行计划，具体方法如下：

1）找出报错时间段对应的snap_id

select s.snap_id,s.begin_interval_time,s.end_interval_time from dba_hist_snapshot s

 

2）找出对用时间范围内占用temp大的sql

select a.temp_space_allocated,a.sql_id,a.sql_plam_hash_value,a.* from dba_hist_active_sess_history a where a.snap_id = xxx order by 1 desc nulls last;

xxx为第一步查询到对应时间段的snap_id

3）根据第二步查询到的前几行进行如下查询，查找到占用空间大的sql

select * from table(dbms_xplan.display_awr(sql_id =>’’,plan_hash_value=>’’))

sql_id与sql_plan_hash_value两个入参分别为第二步查询到的。

查找到占用空间大的sql可以对其优化解决临时表空间经常不足的问题。