oracle 9i 臨時表空間問題

摘自： http://hi.baidu.com/zzztou/blog/item/f8733707008383c87b8947b3.html

 

      正常來說，在完成Select語句、create index等一些使用TEMP表空間的排序操作後，Oracle是會自動釋放掉臨時段a的。但有些時候我們則會遇到臨時段沒有被釋放，TEMP表空間幾乎滿的狀況，甚至是我們重啟了資料庫仍沒有解決問題。這個問題在論壇中也常被網友問到，下面我總結一下，給出幾種處理方法。

法一、重啟庫
      庫重啟時，Smon程式會完成臨時段釋放,TEMP表空間的清理操作，不過很多的時侯我們的庫是不允許down的,所以這種方法缺少了一點的應用機會，不過這種方法還是很好用的。
法二、Metalink給出的一個方法
     修改一下TEMP表空間的storage引數，讓Smon程式觀注一下臨時段，從而達到清理和TEMP表空間的目的。
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;
法三、我常用的一個方法，具體內容如下 ：
1、 使用如下語句a檢視一下認誰在用臨時段
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

2、    那些正在使用臨時段的程式
SQL>Alter system kill session 'sid,serial#';
3、把TEMP表空間回縮一下
SQL>Alter tablespace TEMP coalesce;
法四、使用診斷事件的一種方法,也是被我認為是“殺手鐗”的一種方法
1、 確定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

2、 執行清理操作
SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4' ;
說明：
temp表空間的TS# 為 3*, So TS#+ 1= 4

其它：
1、 出現如上問題的原因我認為可能是由於大的排序超出了TEMP表空間的空間允許範圍引起的。也可能包含著其它的異常的因素。
2、 觀注TEMP等這些空間的狀態是Dba日常職責之一，我們可以通過Toad、Object Browser等這些工具辦到，也可以用如下的語句：

SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
        D.TOT_GROOTTE_MB "表空間大小(M)",
        D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
        TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                      2),
                '990.99') "使用比",
        F.TOTAL_BYTES "空閒空間(M)",
        F.MAX_BYTES "最大塊(M)"
   FROM (SELECT TABLESPACE_NAME,
                ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
                ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
           FROM SYS.DBA_FREE_SPACE
          GROUP BY TABLESPACE_NAME) F,
        (SELECT DD.TABLESPACE_NAME,
                ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
           FROM SYS.DBA_DATA_FILES DD
          GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC

Oracle9i引入了全域性預設臨時表空間,預設的如果不指定使用者臨時表空間,Oracle會為使用者指定這個預設臨時表空間.
首先查詢使用者的預設臨時表空間:
[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 12 11:11:43 2006
Copyright (c) 1982, 2002, Oracle Corporation.   All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> select username,temporary_tablespace from dba_users;
USERNAME                        TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS                                  TEMP2
SYSTEM                          TEMP2
OUTLN                            TEMP2
EYGLE                             TEMP2
CSMIG                             TEMP2
TEST                                TEMP2
REPADMIN                     TEMP2
......
13 rows selected.
SQL> select name from v$tempfile;
NAME
---------------------------------------------------------------------
/opt/oracle/oradata/conner/temp02.dbf
/opt/oracle/oradata/conner/temp03.dbf

重建新的臨時表空間並進行切換:
SQL> create temporary tablespace temp tempfile '/opt/oracle/oradata/conner/temp1.dbf' size 10M;
Tablespace created.
SQL> alter tablespace temp add tempfile '/opt/oracle/oradata/conner/temp2.dbf' size 20M;
Tablespace altered.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> select username,temporary_tablespace from dba_users;
USERNAME                        TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS                                  TEMP
SYSTEM                          TEMP
OUTLN                            TEMP
EYGLE                             TEMP
CSMIG                            TEMP
TEST                                TEMP
REPADMIN                     TEMP
.......
13 rows selected.

如果原臨時表空間無使用者使用,我們可以刪除該表空間:
SQL> drop tablespace temp2;
Tablespace dropped.
SQL>
SQL> select name from v$tempfile;
NAME
---------------------------------------------------------------
/opt/oracle/oradata/conner/temp1.dbf
/opt/oracle/oradata/conner/temp2.dbf
SQL> select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible
   2   from dba_temp_files
   3   /
FILE_NAME                               TABLESPACE_NAME               MB AUTOEXTENSIBLE
-------------------------------------- -------------------- ---------- --------------
/opt/oracle/oradata/conner/temp2.dbf    TEMP                          20 NO
/opt/oracle/oradata/conner/temp1.dbf    TEMP                          10 NO

我們知道Oracle臨時表空間主要是用來做查詢和存放一些快取的資料的，   
   磁碟消耗的一個主要原因是需要對查詢的結果進行排序，如果沒有猜錯的話，   
   在磁碟空間的（記憶體）的分配上，Oracle使用的是貪心演算法，   
   如果上次磁碟空間消耗達到1GB，那麼臨時表空間就是1GB，   
   如果還有增長，那麼依此類推，臨時表空間始終保持在一個最大的上限。   
   像上文提到的恐怖現象經過分析可能是以下幾個方面的原因造成的。   
        
   1.    沒有為臨時表空間設定上限，而是允許無限增長。但是如果設定了一個上限，   
   最後可能還是會面臨因為空間不夠而出錯的問題，臨時表空間設定太小會影響效能，   
   臨時表空間過大同樣會影響效能，至於需要設定為多大需要仔細的測試。   
      
   2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候，   
   根據查詢的欄位和表的個數會生成一個迪斯卡爾積，這個迪斯卡爾積的大小就是一次查詢需要的臨時空間的大小，   
   如果查詢的欄位過多和資料過大，那麼就會消耗非常大的臨時表空間。   
    
   3.對查詢的某些欄位沒有建立索引。Oracle中，如果表沒有索引，那麼會將所有的資料都複製到臨時表空間，   
   而如果有索引的話，一般只是將索引的資料複製到臨時表空間中。   
    
   參照以上原因嘗試解決下,   
   如果不行就清空臨時表空間,不過還是建議先查詢臨時表空間增長的原因才是關鍵   
    
   清控臨時表空間   
   1.startup    --啟動資料庫   
    
   2.create    temporary    tablespace    TEMP2    TEMPFILE    '/home2/oracle/oradata/sysmon/temp02.dbf'    SIZE    512M    REUSE    AUTOEXTEND    ON    NEXT    640K    MAXSIZE    UNLIMITED;    --建立中轉臨時表空間   
    
   3.alter    database    default    temporary    tablespace    temp2;--改變預設臨時表空間    為剛剛建立的新臨時表空間temp2   
    
   4.drop    tablespace    temp    including    contents    and    datafiles;--刪除原來臨時表空間   
    
   5.create    temporary    tablespace    TEMP    TEMPFILE    '/home2/oracle/oradata/sysmon/temp01.dbf'    SIZE    512M    REUSE    AUTOEXTEND    ON    NEXT    640K    MAXSIZE    UNLIMITED;    --重新建立臨時表空間   
    
   6.alter    database    default    temporary    tablespace    temp;--重置預設臨時表空間為新建的temp表空間   
    
   7.drop    tablespace    temp2    including    contents    and    datafiles;--刪除中轉用臨時表空間   
    
   8.alter    user    roll    temporary    tablespace    temp;    --重新指定使用者表空間為重建的臨時表空間