ORACLE臨時表空間的總結分析

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的介紹如下

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.

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，如下圖所示:

管理臨時表空間

創建臨時表空間

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

http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_75a.htm

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11366

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.

SQL> ALTER DATABASE TEMPFILE

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

Database altered.

默認臨時表空間并不能脫機,否則會報錯，如下所示

SQL> ALTER TABLESPACE TEMP OFFLINE;

ALTER TABLESPACE TEMP OFFLINE

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

設置文件自動擴展

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

2 AUTOEXTEND ON

3 NEXT 100M

4 MAXSIZE UNLIMITED;

移動重命名文件

例如，我需要將/u01/app/oracle/oradata/GSP/temp4.dbf這個文件重命名為/u01/app/oracle/oradata/GSP/temp04.dbf

1: 將臨時表空間的臨時文件脫機

SQL> ALTER DATABASE TEMPFILE

  2 '/u01/app/oracle/oradata/GSP/temp4.dbf' OFFLINE;

2：移動或重命名相關的臨時文件

mv /u01/app/oracle/oradata/GSP/temp4.dbf /u01/app/oracle/oradata/GSP/temp04.dbf'

3: 使用腳本ALTER DATABASE RENAME FILE

SQL> ALTER DATABASE RENAME FILE

  2 '/u01/app/oracle/oradata/GSP/temp4.dbf' TO

  3 '/u01/app/oracle/oradata/GSP/temp04.dbf';

4: 將臨時表空間的臨時文件聯機

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp04.dbf' ONLINE;

Database altered.

刪除臨時表空間

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

注意：不能刪除當前用戶的默認表空間，否則會報ORA-12906錯誤

SQL> DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS

*

ERROR at line 1:

ORA-12906: cannot drop default temporary tablespace

如果需要刪除某一個默認的臨時表空間，則必須先創建一個臨時表空間，然后指定新創建的表空間為默認表空間，然后刪除原來的臨時表空間

臨時表空間組

臨進表空間組:

臨進表空間組是ORACLE 10g引入的一個新特性，它是一個邏輯概念，不需要顯示的創建和刪除。只要把一個臨時表空間分配到一個組中，臨時表空間組就自動創建，所有的臨時表空間從臨時表空間組中移除就自動刪除。

一個臨時表空間組必須由至少一個臨時表空間組成，并且無明確的最大數量限制.

A temporary tablespace group contains at least one tablespace. There is no limit for a group to have a maximum number of tablespaces

如果刪除一個臨時表空間組的所有成員，該組也自動被刪除。

臨時表空間的名字不能與臨時表空間組的名字相同。

It shares the namespace of tablespaces, thus its name cannot be the same as that of any tablespace.

可以在創建臨時表空間是指定表空間組，即隱式創建。

SQL>CREATE TEMPORARY TABLESPACE TEMP2

    TEMPFILE '/u01/app/oracle/oradata/GSP/temp2_1.dbf' SIZE 200M

    TABLESPACE GROUP GRP_TEMP;

查看臨時表空間組：

SQL> SELECT * FROM DBA_TABLESPACE_GROUPS;

GROUP_NAME                            TABLESPACE_NAME

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

GRP_TEMP                                  TEMP2

也可以指定已經創建好的臨時表空間的臨時表空間組。

SQL> ALTER TABLESPACE  TEMP TABLESPACE GROUP GRP_TEMP;

    Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME

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

GRP_TEMP                       TEMP

GRP_TEMP                       TEMP2

從組中移除：

SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP '';

當為數據庫指定臨時表空間或為用戶指定臨時表空間時，可以使用臨時表空間組的名稱

ALTER USER DM TEMPORARY TABLESPACE GRP_TEMP;

切換臨時表空間

1：查看舊臨時表空間信息

SELECT * FROM V$TEMPFILE

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS

2：創建中轉的臨時表空間

3：添加相應的數據文件

4：切換臨時表空間。

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;

5：刪除舊的臨時表空間數據文件

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

6：如果有必要，重新指定用戶臨時表空間為新建的臨時表空間

ALTER USER ODS TEMPORARY TABLESPACE TMP;

ALTER USER EDS TEMPORARY TABLESPACE TMP;

ALTER USER ETL TEMPORARY TABLESPACE TMP;

ALTER USER DM TEMPORARY TABLESPACE TMP;

收縮臨時表空間

排序等操作使用的臨時段，使用完成后會被標記為空閑，表示可以重用，占用的空間不會立即釋放，有時候臨時表空間會變得非常大，此時可以通過收縮臨時表空間來釋放沒有使用的空間。收縮臨時表空間是ORACLE 11g新增的功能。

SQL> ALTER TABLESPACE TEMP SHRINK SPACE KEEP 8G;

SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf'

監控臨時表空間

查看臨時表空間使用情況：

SELECT TU.TABLESPACE_NAME AS "TABLESPACE_NAME",

       TT.TOTAL - TU.USED AS "FREE(G)",

       TT.TOTAL AS "TOTAL(G)",

       ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3) AS "USED(%)",

       ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"

FROM (SELECT TABLESPACE_NAME,

              SUM(BYTES_USED) / 1024 / 1024 / 1024 USED

       FROM GV_$TEMP_SPACE_HEADER

       GROUP BY TABLESPACE_NAME) TU ,

     (SELECT TABLESPACE_NAME,

              SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL

       FROM DBA_TEMP_FILES

       GROUP BY TABLESPACE_NAME) TT

WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

COL TEMP_FILE FOR A60;

SELECT ROUND((F.BYTES_FREE  + F.BYTES_USED)/1024/1024/1024, 2) AS "TOTAL(GB)",

       ROUND(((F.BYTES_FREE  + F.BYTES_USED) - NVL(P.BYTES_USED, 0))/1024/1024/1024,2) AS "FREE(GB)",

       D.FILE_NAME AS "TEMP_FILE",

       ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)" ,

       ROUND((F.BYTES_USED + F.BYTES_FREE)/1024/1024/1024, 2) AS "TOTAL(GB)",

       ROUND(((F.BYTES_USED + F.BYTES_FREE) - NVL(P.BYTES_USED, 0))/1024/1024/1024, 2) AS "FREE(GB)" ,

       ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)"

FROM SYS.V_$TEMP_SPACE_HEADER F ,DBA_TEMP_FILES D ,SYS.V_$TEMP_EXTENT_POOL P

WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME

  AND F.FILE_ID(+) = D.FILE_ID

  AND P.FILE_ID(+) = D.FILE_ID;

查看臨時表空間對應的臨時文件的使用情況

SELECT TABLESPACE_NAME AS TABLESPACE_NAME    ,

    BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED  ,

    BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREE

FROM V$TEMP_SPACE_HEADER

ORDER BY 1 DESC;

查找消耗臨時表空間資源比較多的SQL語句

SELECT se.username,

         se.sid,

         su.extents,

         su.blocks * to_number(rtrim(p.value)) as Space,

         tablespace,

         segtype,

         sql_text

FROM v$sort_usage su, v$parameter p, v$session se, v$sql s

   WHERE p.name = 'db_block_size'

     AND su.session_addr = se.saddr

     AND s.hash_value = su.sqlhash

     AND s.address = su.sqladdr

ORDER BY se.username, se.sid;

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。