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.
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;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2145022/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 臨時表空間操作總結
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- Oracle 臨時表空間概念Oracle
- oracle臨時表空間組Oracle
- oracle的臨時表空間Oracle
- Oracle Temp 臨時表空間Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- oracle臨時表空間相關Oracle
- Oracle TEMP臨時表空間概念Oracle
- Oracle 臨時表空間的概念Oracle
- oracle 重建臨時表空間 tempfileOracle
- 刪掉Oracle臨時表空間Oracle
- ORACLE臨時表空間的清理Oracle
- ORACLE臨時表總結Oracle
- oracle 表空間，臨時表空間使用率查詢Oracle
- Oracle Temp臨時表空間處理Oracle
- ORACLE預設的臨時表空間Oracle
- oracle的臨時表空間temporary tablespaceOracle
- oracle清理和重建臨時表空間Oracle
- Oracle修改預設表空間和預設臨時表空間Oracle
- Oracle基礎 02 臨時表空間 tempOracle
- oracle之臨時表空間的收縮Oracle
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 批量處理時臨時增加回滾表空間臨時表空間檔案
- oracle臨時表的用法總結Oracle
- 臨時表空間的建立、刪除，設定預設臨時表空間
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- Oracle表空間維護總結Oracle
- oracle 臨時表空間的增刪改查Oracle
- Oracle效能優化：收縮臨時表空間Oracle優化
- ORACLE 臨時表空間的增刪改查：Oracle
- oracle 9i 臨時表空間問題Oracle
- Oracle下查詢臨時表空間佔用率Oracle
- oracle 臨時表空間基本常識和操作Oracle
- oracle臨時表空間的增刪改查Oracle
- 【臨時表空間組】臨時表空間組的建立、維護及應用
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用