臨時表空間過大的解決方法

Temporary tablespace是oracle裡臨時表空間，臨時表空間主要用途是在資料庫進行排序運算、管理索引、訪問檢視等操作時提供臨時的運算空間，當運算完成之後系統會自動清理。當oracle裡需要用到sort的時候，PGA中sort_area_size大小不夠時，將會把資料放入臨時表空間裡進行排序，同時如果有異常情況的話，也會被放入臨時表空間,正常來說，在完成Select語句、create index等一些使用TEMP表空間的排序操作後，Oracle是會自動釋放掉臨時段的。
但有些有侯我們則會遇到臨時段沒有被釋放，TEMP表空間幾乎滿的狀況，甚至是我們重啟了資料庫仍沒有解決問題。下面介紹一下此種情況的處理方法。
yang@rac1>conn system/yang as sysdba
已連線。
sys@rac1>@showtbs.sql

TABLESPACE_NAME  STATUS    CONTENTS  EXTENT_MAN   total(M)    free(M)    used(M) utilization%
---------------- --------- --------- ---------- ---------- ---------- ---------- ------------
SYSAUX           ONLINE    PERMANENT LOCAL             683         35        648  94.83
UNDOTBS1         ONLINE    UNDO      LOCAL              80         50         30  37.89
USERS            ONLINE    PERMANENT LOCAL            1231       1228          4  .29
SYSTEM           ONLINE    PERMANENT LOCAL            1170        416        754  64.41
EXAMPLE          ONLINE    PERMANENT LOCAL             300        222         78  25.9
TEMP             ONLINE    TEMPORARY LOCAL              97          1         96  98.97

已選擇6行。

TABLESPACE_NAME      BLOCK_SIZE CONTENTS  EXTENT_MAN ALLOCATIO SEGMEN STATUS    LOGGING   FOR PLU
-------------------- ---------- --------- ---------- --------- ------ --------- --------- --- ---
SYSTEM                     8192 PERMANENT LOCAL      SYSTEM    MANUAL ONLINE    LOGGING   NO  NO
SYSAUX                     8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    LOGGING   NO  NO
UNDOTBS1                   8192 UNDO      LOCAL      SYSTEM    MANUAL ONLINE    LOGGING   NO  NO
TEMP                       8192 TEMPORARY LOCAL      UNIFORM   MANUAL ONLINE    NOLOGGING NO  NO
USERS                      8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    LOGGING   NO  NO
EXAMPLE                    8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    NOLOGGING NO  YES

已選擇6行。

TABLESPACE_NAME      INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
-------------------- -------------- ----------- ----------- ----------- ------------
SYSTEM                        65536                       1  2147483645
SYSAUX                        65536                       1  2147483645
UNDOTBS1                      65536                       1  2147483645
TEMP                        1048576     1048576           1                        0
USERS                         65536                       1  2147483645
EXAMPLE                       65536                       1  2147483645

已選擇6行。
解決辦法：
1 建立一個可共替換的臨時表空間temp02
sys@rac1>create temporary tablespace temp02 tempfile 'd:\oracle\oradata\oracl\temp02.dbf' size 512m
autoextend on next 1m maxsize unlimited;
表空間已建立。
2 更改資料庫的預設臨時表空間
sys@rac1>alter database default temporary tablespace temp02;
資料庫已更改。
3 刪除使用過大的臨時表空間
sys@rac1>drop tablespace temp including contents and datafiles;
表空間已刪除。
4 建立新的臨時表空間
sys@rac1>create temporary tablespace temp tempfile 'd:\oracle\oradata\oracl\temp01.dbf' size 512m au
toextend on next 1m maxsize unlimited;
表空間已建立。
5 重置資料庫的臨時表空間
sys@rac1>alter database default temporary tablespace temp;
資料庫已更改。
6 刪除臨時替代的臨時表空間。
sys@rac1>drop tablespace temp02 including contents and datafiles;
表空間已刪除。
sys@rac1>@showtbs.sql
TABLESPACE_NAME      STATUS    CONTENTS  EXTENT_MAN   total(M)    free(M)    used(M) utilization%
-------------------- --------- --------- ---------- ---------- ---------- ---------- ------------
SYSAUX               ONLINE    PERMANENT LOCAL             683         35        648        94.83
UNDOTBS1             ONLINE    UNDO      LOCAL              80         50         30        37.89
USERS                ONLINE    PERMANENT LOCAL            1231       1228          4          .29
SYSTEM               ONLINE    PERMANENT LOCAL            1170        416        754        64.41
EXAMPLE              ONLINE    PERMANENT LOCAL             300        222         78         25.9
TEMP                 ONLINE    TEMPORARY LOCAL             512

已選擇6行。
TABLESPACE_NAME      BLOCK_SIZE CONTENTS  EXTENT_MAN ALLOCATIO SEGMEN STATUS    LOGGING   FOR PLU
-------------------- ---------- --------- ---------- --------- ------ --------- --------- --- ---
SYSTEM                     8192 PERMANENT LOCAL      SYSTEM    MANUAL ONLINE    LOGGING   NO  NO
SYSAUX                     8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    LOGGING   NO  NO
UNDOTBS1                   8192 UNDO      LOCAL      SYSTEM    MANUAL ONLINE    LOGGING   NO  NO
TEMP                       8192 TEMPORARY LOCAL      UNIFORM   MANUAL ONLINE    NOLOGGING NO  NO
USERS                      8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    LOGGING   NO  NO
EXAMPLE                    8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    NOLOGGING NO  YES
已選擇6行。
TABLESPACE_NAME      INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
-------------------- -------------- ----------- ----------- ----------- ------------
SYSTEM                        65536                       1  2147483645
SYSAUX                        65536                       1  2147483645
UNDOTBS1                      65536                       1  2147483645
TEMP                        1048576     1048576           1                        0
USERS                         65536                       1  2147483645
EXAMPLE                       65536                       1  2147483645

已選擇6行。
sys@rac1>