Temporary表空间100%解决方案(原创)

在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放临时段。但有些有侯我们则会遇到临时段没有被释放，TEMP表空间几乎满的状况，甚至是我们重启了数据库仍没有解决问题。下面总结出几种处理方法：
重启库
数据库重启时，Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。

修改TEMP表空间的storage参数
这个方法来自Metalink主要是让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;
以上方法在9I和10G不行，
SQL> alter tablespace temp PCTINCREASE 1;
alter tablespace temp PCTINCREASE 1
*
第 1 行出现错误:
ORA-02142: ALTER TABLESPACE 选项缺失或无效
杀死会话，回收收缩

句a查看一下认谁在用临时段
SQL>SELECT se.username,
  sid,
  serial#,
  sql_address,
  machine,
  program,
  tablespace,
  segtype,
  contents
FROM v$session se,
  v$sort_usage su
WHERE se.saddr=su.session_addr
杀死正在使用临时段的进程
SQL>Alter system kill session 'sid,serial#';
回缩TEMP表空间
SQL>Alter tablespace TEMP coalesce;
使用诊断事件
确定TEMP表空间的ts#
SQL>select ts#, name from sys.ts$ ;
TS# NAME
-----------------------
0 SYSYEM
1 RBS
2 USERS
3* TEMP
4 TOOLS
5 INDX
6 DRSYS
执行清理操作
SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4' ;
说明：
temp表空间的TS# 为 3*, So TS#+ 1= 4
一下是摘自Metalink的说明
The DROP_SEGMENTS event
~~~~~~~~~~~~~~~~~~~~~~~
 Available from 8.0 onwards.
 
   DESCRIPTION
     Finds all the temporary segments in a tablespace which are not
     currently locked and drops them.
     For the purpose of this event a "temp" segment is defined as a
     segment (seg$ entry) with TYPE#=3. Sort space in a TEMPORARY
     tablespace does not qualify under this definition as such
     space is managed independently of SEG$ entries.

   PARAMETERS
     level - tablespace number+1. If the value is 2147483647 then
             temp segments in ALL tablespaces are dropped, otherwise, only
             segments in a tablespace whose number is equal to the LEVEL
             specification are dropped.

   NOTES
     This routine does what SMON does in the background, i.e. drops
     temporary segments. It is provided as a manual intervention tool which
     the user may invoke if SMON misses the post and does not get to
     clean the temp segments for another 2 hours. We do not know whether
     missed post is a real possibility or more theoretical situation, so
     we provide this event as an insurance against SMON misbehaviour.

     Under normal operation there is no need to use this event.

     It may be a good idea to
        alter tablespace <tablespace> coalesce;
     after dropping lots of extents to tidy things up.

 *SQL Session (if you can connect to the database):     
    alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';

     The TS# can be obtained from v$tablespace view:
     select ts# from v$tablespace where name = '<Tablespace name>';

     Or from SYS.TS$:

     select ts# from sys.ts$ where name = '<Tablespace name>' and online$ != 3;
    
     If ts# is 5, an example of dropping the temporary segments in that tablespace
     would be:

    alter session set events 'immediate trace name DROP_SEGMENTS level 6';
临时表空间相关常用sql

查看临时表空间的使用情况（GV_$TEMP_SPACE_HEADER视图必须在sys用户下才能查询）
GV_$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小
dba_temp_files视图的bytes字段记录的是临时表空间的总大小
SELECT temp_used.tablespace_name,
       total - used as "Free",
       total as "Total",
       round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
          FROM GV$TEMP_SPACE_HEADER
         GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_temp_files
         GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name

查找比较消耗临时表空间资源的sql语句
Select se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as Space,
       tablespace,
       segtype,
       sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
 order by se.username, se.sid

查看当前临时表空间使用大小与正在占用临时表空间的sql语句
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;
个人认为，临时表空间组可以有效减少临时表空间使用率100%的情况

具体可参见
http://czmmiao.iteye.com/blog/1293790

 

参考至：EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments [ID 47400.1]
               http://www.cnblogs.com/lanzi/archive/2011/06/08/2075447.html

               http://hi.baidu.com/yafeiie/blog/item/8272452775690606908f9d44.html

               http://www.tbdata.org/archives/203

本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:[e-mail]