ORACLE臨時表空間總結

臨時表空間概念

 

臨時表空間用來管理資料庫排序操作以及用於儲存臨時表、中間排序結果等臨時物件,當ORACLE裡需要用到SORT的時候，並且當PGA中sort_area_size大小不夠時，將會把資料放入臨時表空間裡進行排序。像資料庫中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能會用到臨時表空間。當操作完成後，系統會自動清理臨時表空間中的臨時物件，自動釋放臨時段。這裡的釋放只是標記為空閒、可以重用，其實實質佔用的磁碟空間並沒有真正釋放。這也是臨時表空間有時會不斷增大的原因。

臨時表空間儲存大規模排序操作(小規模排序操作會直接在RAM裡完成，大規模排序才需要磁碟排序Disk Sort)和雜湊操作的中間結果.它跟永久表空間不同的地方在於它由臨時資料檔案（temporary files）組成的,而不是永久資料檔案（datafiles）。臨時表空間不會儲存永久型別的物件，所以它不會也不需要備份。另外，對臨時資料檔案的操作不產生redo日誌，不過會生成undo日誌。

建立臨時表空間或臨時表空間新增臨時資料檔案時，即使臨時資料檔案很大，新增過程也相當快。這是因為ORACLE的臨時資料檔案是一類特殊的資料檔案:稀疏檔案（Sparse File）,當臨時表空間檔案建立時，它只會寫入檔案頭部和最後塊資訊(only writes to the header and last block of the file)。它的空間是延後分配的.這就是你建立臨時表空間或給臨時表空間新增資料檔案飛快的原因。

另外，臨時表空間是NOLOGGING模式以及它不儲存永久型別物件，因此即使資料庫損毀，做Recovery也不需要恢復Temporary Tablespace。

臨時表空間資訊

 

檢視例項的臨時表空間

SQL1：

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE

2 FROM DATABASE_PROPERTIES

3 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                    PROPERTY_VALUE

------------------------------ ----------------------------

DEFAULT_TEMP_TABLESPACE               TEMP

SQL2：

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

 

檢視臨時表空間資訊：

 

SET LINESIZE 1200
COL NAME FOR A60
SELECT FILE# AS FILE_NUMBER
    ,NAME AS NAME
    ,CREATION_TIME AS CREATION_TIME
    ,BLOCK_SIZE AS BLOCK_SIZE
    ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
    ,CREATE_BYTES/1024/1024/1024 AS "INIT_SIZE(G)"
    ,STATUS AS STATUS
    ,ENABLED AS ENABLED
FROM V$TEMPFILE;

 

官方文件關於V$TEMPFILE的介紹如下

Column	Datatype	Description
FILE#	NUMBER	Absolute file number
CREATION_CHANGE#	NUMBER	Creation System Change Number (SCN)
CREATION_TIME	DATE	Creation time
TS#	NUMBER	Tablespace number
RFILE#	NUMBER	Relative file number in the tablespace
STATUS	VARCHAR2(7)	Status of the file (OFFLINE|ONLINE)
ENABLED	VARCHAR2(10)	Enabled for read and/or write
BYTES	NUMBER	Size of the file in bytes (from the file header)
BLOCKS	NUMBER	Size of the file in blocks (from the file header)
CREATE_BYTES	NUMBER	Creation size of the file (in bytes)
BLOCK_SIZE	NUMBER	Block size for the file
NAME	VARCHAR2(513)	Name of the file

SET LINESIZE 1200
    COL TABLESPACE_NAME FOR A30
   COL FILE_NAME FOR A60
SELECT TABLESPACE_NAME AS TABLESPACE_NAME
        ,FILE_NAME AS FILE_NAME
        ,BLOCKS AS BLOCKS
        ,STATUS AS STATUS
        ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE
        ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
        ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024,
                          MAXBYTES/1024/1024/1024)
                                       AS "MAX_SIZE(G)"
        ,INCREMENT_BY AS "INCREMENT_BY"
        ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE"
FROM DBA_TEMP_FILES;

DBA_TEMP_FILES describes all temporary files (tempfiles) in the database.

Column	Datatype	NULL	Description
FILE_NAME	VARCHAR2(513)		Name of the database temp file
FILE_ID	NUMBER		File identifier number of the database temp file
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace to which the file belongs
BYTES	NUMBER		Size of the file (in bytes)
BLOCKS	NUMBER		Size of the file (in Oracle blocks)
STATUS	CHAR(9)		File status: · · AVAILABLE
RELATIVE_FNO	NUMBER		Tablespace-relative file number
AUTOEXTENSIBLE	VARCHAR2(3)		Indicates whether the file is autoextensible (YES) or not (NO)
MAXBYTES	NUMBER		maximum size of the file (in bytes)
MAXBLOCKS	NUMBER		Maximum size of the file (in Oracle blocks)
INCREMENT_BY	NUMBER		Default increment for autoextension
USER_BYTES	NUMBER		Size of the useful portion of the file (in bytes)
USER_BLOCKS	NUMBER		Size of the useful portion of the file (in Oracle blocks)

 

SQL> SELECT BYTES,BLOCKS,  USER_BYTES, USER_BLOCKS,
            BLOCKS -USER_BLOCKS AS SYSTEM_USED
     FROM DBA_TEMP_FILES;

     BYTES     BLOCKS USER_BYTES USER_BLOCKS SYSTEM_USED
---------- ---------- ---------- ----------- -----------
2147483648     262144 2146435072      262016         128
1073741824     131072 1072693248      130944         128
 209715200      25600  208666624       25472         128

 

這四列中， BYTES , BLOCKS 顯示的是臨時檔案有多少BYTE大小，包含多少個資料塊。而USER_BYTES,USER_BLOCKS是可用的BYTE和資料塊個數。因此，我們可以知道臨時檔案中有一部分是被系統佔用的，大概可以理解成檔案頭資訊，這一部分大小是128個block，如下圖所示:

 

管理臨時表空間

 

建立臨時表空間

下面是一個簡單的建立臨時表空間的例子，具體很多細節可以參考官方文件，這裡省略,不做過多介紹。

 

CREATE TEMPORARY TABLESPACE TMP

TEMPFILE '/u01/gsp/oradata/TMP01.dbf'

SIZE 8G

AUTOEXTEND OFF;

 

增加資料檔案

當臨時表空間太小時，就需要擴充套件臨時表空間（新增資料檔案、增大資料檔案、設定檔案自動擴充套件）；有時候需要將臨時資料檔案分佈到不同的磁碟分割槽中，提升IO效能，也需要透過刪除、增加臨時表空間資料檔案。

 

SQL> ALTER TABLESPACE TEMP
  2 ADD TEMPFILE '/u04/gsp/oradata/temp02.dbf'
  3 SIZE 4G
  4  AUTOEXTEND ON
  5 NEXT 128M
  6  MAXSIZE 6G;

Tablespace altered.


SQL> ALTER TABLESPACE TMP
ADD TEMPFILE '/u03/eps/oradata/temp02.dbf'
SIZE 64G
AUTOEXTEND OFF;

Tablespace altered.

 

刪除資料檔案

 

例如，我想刪除臨時表空間下的某個檔案，那麼我們有兩種方式刪除臨時表空間的資料檔案。

方法1：

SQL> ALTER TABLESPACE TEMP

     DROP TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf';

Tablespace altered.

注意：這種刪除臨時表空間的寫法會將對應的物理檔案刪除。

方法2：

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf'

DROP INCLUDING DATAFILES;

Database altered.

注意：刪除臨時表空間的臨時資料檔案時，不需要指定INCLUDING DATAFILES 選項也會真正刪除物理檔案，否則需要手工刪除物理檔案。

 

調整檔案大小

 

如下例子，需要將臨時資料檔案從1G大小調整為2G

SQL> ALTER DATABASE TEMPFILE

'/u01/app/oracle/oradata/GSP/temp02.dbf' RESIZE 2G;

 

檔案離線聯機

 

SQL> ALTER DATABASE TEMPFILE
  2 '/u01/app/oracle/oradata/GSP/temp02.dbf' OFFLINE;

Database altered.