【TEMP】臨時表空間的工作原理及維護方法

一、臨時表空間工作原理

    使用者很多操作都會使用到臨時表空間的臨時段，最常見的是排序操作。當使用者執行排序操作時，有兩個區域可以容納該操作：

    1、優先在記憶體中進行，稱為Sort Memory。如果啟用自動PGA管理，則受到引數PGA_AGGREGATE_TARGET的影響；如果PGA是手動管理的，則用於排序的記憶體空間受引數SORT_AREA_SIZE影響。

    2、其次使用臨時表空間，稱為Sort Disk。當記憶體無法容納該排序操作時，則會使用到臨時表空間進行排序。Sort Memory的效能遠高於Sort Disk。

SYS@MTH> select name,value from v$sysstat where name like 'sort%';

NAME                      VALUE
--------------------     ----------

sorts (memory)           3051115172
sorts (disk)             662
sorts (rows)             2.1834E+10

    

    當第一次使用臨時表空間的排序操作開始後，臨時段被建立，extent被分配到這個臨時段中供排序操作使用；當排序結束後，這個臨時段並不會被刪除，而是將這個臨時段中的extent標記為free,其他排序操作可以繼續使用這個臨時段。所以，臨時段在資料庫中是一次分配，迴圈使用。

    在一個資料庫例項中，同一個臨時表空間只存在一個臨時段，Oracle根據排序空間需求，逐漸分配extent到這個臨時段中。當資料庫重啟後，SMON會釋放臨時段中的extent。

二、重建與切換TEMP表空間

1、首先檢視當前的預設TEMP表空間

SYS@MTH> select tablespace_name,file_name,bytes/1024/1024 MB from dba_temp_files;

TABLESPACE_NAME                FILE_NAME                              MB
------------------------------ ------------------------------ ----------
TEMP                           /oraidx/MTH/temp01.dbf                200

SYS@MTH> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
RMAN                           TEMP
MTH                            TEMP
ZYX                            TEMP
EMST                           TEMP
DPGS                           TEMP
EBOARD                         TEMP
SCOTT                          TEMP
......

檢視TEMP表空間使用情況：

SYS@MTH> select tablespace_name,tablespace_size/1024/1024 tablespace_size,allocated_space/1024/1024 allocated_space,free_space/1024/1024 free_space from dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE   ALLOCATED_SPACE   FREE_SPACE
---------------------          ---------------   ---------------   ----------
TEMP                           200               10                198

2、建立新的臨時表空間

SYS@MTH> create temporary tablespace TEMP_NEW tempfile '/oraidx/MTH/temp_new01.dbf' size 20M;

Tablespace created.

3、切換，設定新的臨時表空間為預設臨時表空間

SYS@MTH> alter database default temporary tablespace TEMP_NEW;

Database altered.

4、檢查此時的預設臨時表空間
SYS@MTH> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP_NEW

SYS@MTH> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
RMAN                           TEMP_NEW
MTH                            TEMP_NEW
ZYX                            TEMP_NEW
EMST                           TEMP_NEW
DPGS                           TEMP_NEW
EBOARD                         TEMP_NEW
SCOTT                          TEMP_NEW
FR_ADMIN                       TEMP_NEW
......

5、查詢試圖，如果舊的臨時表空間沒有使用者在使用，就可以刪除。

SYS@MTH> select /*+ rule*/ distinct a.SID, a.PROCESS, a.SERIAL#, to_char(a.LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') login, a.OSUSER,tablespace,b.SQL_TEXT from v$session a, v$sql b, v$sort_usage c where a.SQL_ADDRESS=b.ADDRESS and a.SADDR=c.SESSION_ADDR;

       SID  PROCESS    SERIAL#     LOGIN                OSUSER     TABLESPACE  SQL_TEXT
---------- --------    ---------- -------------------  ----------  ---------- ----------------------------------------------------------------------------------------------------
               9  19054       2289       2017-05-10 11:33:30  oracle      TEMP       select /*+ rule*/ distinct a.SID, a.PROCESS, a.SERIAL#, to_char(a.LOGON_TIME,:"SYS_B_0") login, a.OS
                                                                              USER,tablespace,b.SQL_TEXT from v$session a, v$sql b, v$sort_usage c where a.SQL_ADDRESS=b.ADDRESS a
                                                                              nd a.SADDR=c.SESSION_ADDR

kill掉這個session,

alter system kill session 'sid,serial#'；

重新查詢：

SYS@MTH> select /*+ rule*/ distinct a.SID, a.PROCESS, a.SERIAL#, to_char(a.LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') login, a.OSUSER,tablespace,b.SQL_TEXT from v$session a, v$sql b, v$sort_usage c where a.SQL_ADDRESS=b.ADDRESS and a.SADDR=c.SESSION_ADDR;

no rows selected

刪除舊的臨時表空間：

SYS@MTH> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30776559/viewspace-2138831/，如需轉載，請註明出處，否則將追究法律責任。