ORACLE臨時(shí)表空間的總結(jié)分析

ORACLE臨時(shí)表空間的總結(jié)分析，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

臨時(shí)表空間概念

臨時(shí)表空間用來(lái)管理數(shù)據(jù)庫(kù)排序操作以及用于存儲(chǔ)臨時(shí)表、中間排序結(jié)果等臨時(shí)對(duì)象,當(dāng)ORACLE里需要用到SORT的時(shí)候，并且當(dāng)PGA中sort_area_size大小不夠時(shí)，將會(huì)把數(shù)據(jù)放入臨時(shí)表空間里進(jìn)行排序。像數(shù)據(jù)庫(kù)中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能會(huì)用到臨時(shí)表空間。當(dāng)操作完成后，系統(tǒng)會(huì)自動(dòng)清理臨時(shí)表空間中的臨時(shí)對(duì)象，自動(dòng)釋放臨時(shí)段。這里的釋放只是標(biāo)記為空閑、可以重用，其實(shí)實(shí)質(zhì)占用的磁盤(pán)空間并沒(méi)有真正釋放。這也是臨時(shí)表空間有時(shí)會(huì)不斷增大的原因。

臨時(shí)表空間存儲(chǔ)大規(guī)模排序操作(小規(guī)模排序操作會(huì)直接在RAM里完成，大規(guī)模排序才需要磁盤(pán)排序Disk Sort)和散列操作的中間結(jié)果.它跟永久表空間不同的地方在于它由臨時(shí)數(shù)據(jù)文件（temporary files）組成的,而不是永久數(shù)據(jù)文件（datafiles）。臨時(shí)表空間不會(huì)存儲(chǔ)永久類(lèi)型的對(duì)象，所以它不會(huì)也不需要備份。另外，對(duì)臨時(shí)數(shù)據(jù)文件的操作不產(chǎn)生redo日志，不過(guò)會(huì)生成undo日志。

創(chuàng)建臨時(shí)表空間或臨時(shí)表空間添加臨時(shí)數(shù)據(jù)文件時(shí)，即使臨時(shí)數(shù)據(jù)文件很大，添加過(guò)程也相當(dāng)快。這是因?yàn)镺RACLE的臨時(shí)數(shù)據(jù)文件是一類(lèi)特殊的數(shù)據(jù)文件:稀疏文件（Sparse File）,當(dāng)臨時(shí)表空間文件創(chuàng)建時(shí)，它只會(huì)寫(xiě)入文件頭部和最后塊信息(only writes to the header and last block of the file)。它的空間是延后分配的.這就是你創(chuàng)建臨時(shí)表空間或給臨時(shí)表空間添加數(shù)據(jù)文件飛快的原因。

另外，臨時(shí)表空間是NOLOGGING模式以及它不保存永久類(lèi)型對(duì)象，因此即使數(shù)據(jù)庫(kù)損毀，做Recovery也不需要恢復(fù)Temporary Tablespace。

臨時(shí)表空間信息

查看實(shí)例的臨時(shí)表空間

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;

查看臨時(shí)表空間信息：

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;

官方文檔關(guān)于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 顯示的是臨時(shí)文件有多少BYTE大小，包含多少個(gè)數(shù)據(jù)塊。而USER_BYTES,USER_BLOCKS是可用的BYTE和數(shù)據(jù)塊個(gè)數(shù)。因此，我們可以知道臨時(shí)文件中有一部分是被系統(tǒng)占用的，大概可以理解成文件頭信息，這一部分大小是128個(gè)block，如下圖所示:

管理臨時(shí)表空間

創(chuàng)建臨時(shí)表空間

下面是一個(gè)簡(jiǎn)單的創(chuàng)建臨時(shí)表空間的例子，具體很多細(xì)節(jié)可以參考官方文檔，這里省略,不做過(guò)多介紹。

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;

增加數(shù)據(jù)文件

當(dāng)臨時(shí)表空間太小時(shí)，就需要擴(kuò)展臨時(shí)表空間（添加數(shù)據(jù)文件、增大數(shù)據(jù)文件、設(shè)置文件自動(dòng)擴(kuò)展）；有時(shí)候需要將臨時(shí)數(shù)據(jù)文件分布到不同的磁盤(pán)分區(qū)中，提升IO性能，也需要通過(guò)刪除、增加臨時(shí)表空間數(shù)據(jù)文件。

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.

刪除數(shù)據(jù)文件

例如，我想刪除臨時(shí)表空間下的某個(gè)文件，那么我們有兩種方式刪除臨時(shí)表空間的數(shù)據(jù)文件。

方法1：

SQL> ALTER TABLESPACE TEMP

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

Tablespace altered.

注意：這種刪除臨時(shí)表空間的寫(xiě)法會(huì)將對(duì)應(yīng)的物理文件刪除。

方法2：

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

DROP INCLUDING DATAFILES;

Database altered.

注意：刪除臨時(shí)表空間的臨時(shí)數(shù)據(jù)文件時(shí)，不需要指定INCLUDING DATAFILES 選項(xiàng)也會(huì)真正刪除物理文件，否則需要手工刪除物理文件。

調(diào)整文件大小

如下例子，需要將臨時(shí)數(shù)據(jù)文件從1G大小調(diào)整為2G

SQL> ALTER DATABASE TEMPFILE

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

文件脫機(jī)聯(lián)機(jī)

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.

默認(rèn)臨時(shí)表空間并不能脫機(jī),否則會(huì)報(bào)錯(cuò)，如下所示

SQL> ALTER TABLESPACE TEMP OFFLINE;

ALTER TABLESPACE TEMP OFFLINE

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

設(shè)置文件自動(dòng)擴(kuò)展

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

2 AUTOEXTEND ON

3 NEXT 100M

4 MAXSIZE UNLIMITED;

移動(dòng)重命名文件

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

1: 將臨時(shí)表空間的臨時(shí)文件脫機(jī)

SQL> ALTER DATABASE TEMPFILE

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

2：移動(dòng)或重命名相關(guān)的臨時(shí)文件

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: 將臨時(shí)表空間的臨時(shí)文件聯(lián)機(jī)

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

Database altered.

刪除臨時(shí)表空間

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

注意：不能刪除當(dāng)前用戶(hù)的默認(rèn)表空間，否則會(huì)報(bào)ORA-12906錯(cuò)誤

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

如果需要?jiǎng)h除某一個(gè)默認(rèn)的臨時(shí)表空間，則必須先創(chuàng)建一個(gè)臨時(shí)表空間，然后指定新創(chuàng)建的表空間為默認(rèn)表空間，然后刪除原來(lái)的臨時(shí)表空間

臨時(shí)表空間組

臨進(jìn)表空間組:

臨進(jìn)表空間組是ORACLE 10g引入的一個(gè)新特性，它是一個(gè)邏輯概念，不需要顯示的創(chuàng)建和刪除。只要把一個(gè)臨時(shí)表空間分配到一個(gè)組中，臨時(shí)表空間組就自動(dòng)創(chuàng)建，所有的臨時(shí)表空間從臨時(shí)表空間組中移除就自動(dòng)刪除。

一個(gè)臨時(shí)表空間組必須由至少一個(gè)臨時(shí)表空間組成，并且無(wú)明確的最大數(shù)量限制.

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

如果刪除一個(gè)臨時(shí)表空間組的所有成員，該組也自動(dòng)被刪除。

臨時(shí)表空間的名字不能與臨時(shí)表空間組的名字相同。

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

可以在創(chuàng)建臨時(shí)表空間是指定表空間組，即隱式創(chuàng)建。

SQL>CREATE TEMPORARY TABLESPACE TEMP2

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

    TABLESPACE GROUP GRP_TEMP;

查看臨時(shí)表空間組：

SQL> SELECT * FROM DBA_TABLESPACE_GROUPS;

GROUP_NAME                            TABLESPACE_NAME

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

GRP_TEMP                                  TEMP2

也可以指定已經(jīng)創(chuàng)建好的臨時(shí)表空間的臨時(shí)表空間組。

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 '';

當(dāng)為數(shù)據(jù)庫(kù)指定臨時(shí)表空間或?yàn)橛脩?hù)指定臨時(shí)表空間時(shí)，可以使用臨時(shí)表空間組的名稱(chēng)

ALTER USER DM TEMPORARY TABLESPACE GRP_TEMP;

切換臨時(shí)表空間

1：查看舊臨時(shí)表空間信息

SELECT * FROM V$TEMPFILE

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS

2：創(chuàng)建中轉(zhuǎn)的臨時(shí)表空間

3：添加相應(yīng)的數(shù)據(jù)文件

4：切換臨時(shí)表空間。

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;

5：刪除舊的臨時(shí)表空間數(shù)據(jù)文件

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

6：如果有必要，重新指定用戶(hù)臨時(shí)表空間為新建的臨時(shí)表空間

ALTER USER ODS TEMPORARY TABLESPACE TMP;

ALTER USER EDS TEMPORARY TABLESPACE TMP;

ALTER USER ETL TEMPORARY TABLESPACE TMP;

ALTER USER DM TEMPORARY TABLESPACE TMP;

收縮臨時(shí)表空間

排序等操作使用的臨時(shí)段，使用完成后會(huì)被標(biāo)記為空閑，表示可以重用，占用的空間不會(huì)立即釋放，有時(shí)候臨時(shí)表空間會(huì)變得非常大，此時(shí)可以通過(guò)收縮臨時(shí)表空間來(lái)釋放沒(méi)有使用的空間。收縮臨時(shí)表空間是ORACLE 11g新增的功能。

SQL> ALTER TABLESPACE TEMP SHRINK SPACE KEEP 8G;

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

監(jiān)控臨時(shí)表空間

查看臨時(shí)表空間使用情況：

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;

查看臨時(shí)表空間對(duì)應(yīng)的臨時(shí)文件的使用情況

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;

查找消耗臨時(shí)表空間資源比較多的SQL語(yǔ)句

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;

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝您對(duì)億速云的支持。