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

TaihangMeng發表於2017-05-10

一、臨時表空間工作原理

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

    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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章