臨時表空間使用率過高的解決辦法

1.執行下述SQL，佔用TEMP表空間較大的session資訊一目瞭然

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

GV$SORT_USAGE與GV$TEMPSEG_USAGE可以互相替代。

檢視GV$SORT_USAGE中的SEGTYPE列的不同的值所代表的含義如下所示：

SORT：SQL排序使用的臨時段，包括ORDER BY、GROUP BY、DISTINCT、視窗函式、合併查詢（UNION、INTERSECT、MINUS）、索引的建立（CREATE）和重建（REBUILD）、ANALYZE分析表等產生的排序。

DATA：臨時表（GLOBAL TEMPORARY TABLE）儲存資料使用的段。

INDEX：臨時表上建的索引使用的段。

HASH：HASH演算法，如HASH連線所使用的臨時段。

LOB_DATA和LOB_INDEX：臨時LOB使用的臨時段。

2.分析佔用率過高的會話，確保會話異常或SQL異常後，可以清理該會話：

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

3.會話清理後如表空間不釋放，可採用下述辦法釋放空間：

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。

4.其他簡單粗暴的解決辦法：

1）重啟資料庫。

2）重建表空間：

/**檢視當前的資料庫預設表空間**/

    SQL>select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

/**確認當前的臨時表空間為TEMP**/

/**檢視目前臨時表空間的大小**/

    SQL>select file_name,tablespace_name,bytes/1024/1024 "MB",autoextensible from dba_temp_files；

/**建立另一個臨時表空間**/

    SQL>create temporary tablespace TEMP2 TEMPFILE 'D:\oradata\temp02.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

/**改變預設臨時表空間為剛剛建立的新臨時表空間temp2**/

    SQL>alter database  default temporary tablespace temp2; 

/**刪除原來臨時表空間**/

    SQL>drop tablespace temp including contents and datafiles;

/**重新建立臨時表空間temp **/

    SQL>create temporary tablespace TEMP TEMPFILE 'D:\oradata\temp01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED; 

/**重置預設臨時表空間為新建的temp表空間**/

    SQL>alter database default temporary tablespace temp; 

/**刪除中轉用臨時表空間 **/

    SQL>drop tablespace temp2 including contents and datafiles; 

/**重新指定使用者表空間為重建的臨時表空間 **/

    SQL>alter user yhm temporary tablespace temp;