清理临时段

查看临时段使用情况：

SELECT V.INST_ID,
       V.SID,
       V.SERIAL#,
       V.USERNAME,
       V.STATUS,
       V.ACTION,
       V.MACHINE,
       V.MODULE,
       V.OSUSER,
       V.TERMINAL,
       V.PROGRAM,
       V.SQL_ID,
       SU.TABLESPACE,
       (SU.BLOCKS *
       TO_NUMBER((SELECT RTRIM(VALUE)
                    FROM V$PARAMETER P
                   WHERE P.NAME = 'db_block_size'))) / 1024 / 1024 AS SIZE_M,
       (SELECT ROUND(SUM(BYTES) / (1024 * 1024), 3) FROM V$TEMPFILE) TEMP_TS_SIZE_M,
       ROUND((SU.BLOCKS *
             TO_NUMBER((SELECT RTRIM(VALUE)
                          FROM V$PARAMETER P
                         WHERE P.NAME = 'db_block_size'))) * 100 /
             (SELECT SUM(BYTES)
                FROM V$TEMPFILE),
             3) C_USED_PERCENT,
       SU.SEGTYPE,
       (SELECT A.SQL_TEXT
          FROM GV$SQLAREA A
         WHERE A.SQL_ID = NVL(V.SQL_ID,SU.SQL_ID)
           AND A.INST_ID = V.INST_ID
           AND ROWNUM = 1) SQL_TEXT,
       SU.SEGFILE#,
       SU.SEGBLK#,
       SU.EXTENTS,
       SU.BLOCKS,
       SU.SEGRFNO#
  FROM GV$SORT_USAGE SU, --GV$TEMPSEG_USAGE
       GV$SESSION    V
 WHERE SU.SESSION_ADDR = V.SADDR
   AND SU.INST_ID = V.INST_ID
 ORDER BY SU.INST_ID,  SU.BLOCKS DESC
;
----------------------------------
SELECT username,
       sid,
       serial#,
       sql_address,
       machine,
       program,
       tablespace,
       segtype,
       contents
  FROM v$session se, v$sort_usage su
 WHERE se.saddr = su.session_addr
/

视图GV$SORT_USAGE中的SEGTYPE列的不同的值所代表的含义如下：

1）SORT：SQL 排序使用的临时段，包括 ORDER BY、GROUP BY、DISTINCT、窗口函数（WINDOW FUNCTION，如 ROLLUP）、合并查询（UNION、INTERSECT、MINUS）、索引的创建（CREATE）和重建（REBUILD）、ANALYZE分析表等产生的排序。
2）DATA：临时表（GLOBAL TEMPORARY TABLE）存储数据使用的段。
3）INDEX：临时表上建的索引使用的段。
4）HASH：HASH算法，如HASH连接所使用的临时段。
5）LOB_DATA和LOB_INDEX：临时LOB使用的临时段。
根据上述的段类型，说明TEMP表空间大体可以分为4类占用：①SQL语句排序。②Hash Join占用。③临时表、临时表上的索引占用。④LOB对象占用。

在找到了哪些会话占用临时表空间过大后，分析这些会话，确保会话异常或 SQL 异常后，接着就可以将这些会话清理掉，如下：

alter system kill session 'sid,searial#' immediate;

TEMP表空间回缩：

SQL>Alter tablespace TEMP coalesce;

使用诊断事件来清理临时段：：

select ts# name from v$tablespace where name='temp';
alter session set events 'immediate trace name drop segments level 4';

LEVEL后的值为TS#+1。在以上例子中，TEMP表空间的TS#为3，所以TS#+1=4。如果想清除所有表空间的临时段，那么TS#设置为2147483647。

https://lhrbest.blog.csdn.net/article/details/102763335