临时表空间不释放如何解决？ORA-1652又来了？

目录

一.本次分享主题

     

二、主题分享背景

三.场景排查定位

 

四.灵活运用

五.问题总结

---

 ORA-1652: unable to extend temp segment by 128 in tablespace

一.本次分享主题

     临时表空间不释放如何解决？

二、主题分享背景

  生产上经常听到dba抱怨，temp表空间又干满了？

   咋解决？

·······························

·······························

  又得扩容？

  磁盘用光了，没得空间扩容了呀

三.场景排查定位

3.1 temp表空间使用情况核实

temp临时表空间使用率：
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;

3.2 有哪些用户在使用？占用的sql是啥？

select a.username,
       a.sql_id,
       a.SEGTYPE,
       b.BYTES_USED/1024/1024/1024||'G',
       b.BYTES_FREE/1024/1024/1024  from   V$TEMPSEG_USAGE  a  join  V$TEMP_SPACE_HEADER b on   a.TABLESPACE=b.tablespace_name;

报错深入定位诊断办法：

SELECT S.sid || ',' || S.serial# sid_serial, S.username,T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text 
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address 
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

开启：
ALTER SYSTEM SET EVENTS '1652 TRACE NAME ERRORSTACK LEVEL 3';

alter system set events '1652 trace name errorstack off';
alter system set events '1652 trace name context off';

四.灵活运用

之前知识回顾：

速效大法一：
添加临时表空间：
SQL> alter tablespace temp add tempfile '+DG_DATA/pubdb1/temp02.dbf' size 10G autoextend on;

SQL> alter tablespace temp add tempfile '+DG_DATA/paramdb1/temp02.dbf' size 31G autoextend off;

 alter tablespace temp add tempfile '+DG_DATA/paramdb1/temp03.dbf' size 31G autoextend off;

速效大法二：
回收临时表空间：
alter tablespace temp shrink space;

折中做法三：释放后缩小空间，降低磁盘占用
alter database tempfile '+DG_DATA/paramdb1/temp03.dbf' resize 31G;

五.问题总结

对于lob占用的无法正常释放，方案有多种，现介绍其一：

根据Oracle MOS ( How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (Doc ID 802897.1))文档资料，建议使用：
alter session set events ‘60025 trace name context forever’;

通过设置event 60025来强制会话在COMMIT之后强制释放临时表空间上的LOB。