oracle暫存資料表空間

標籤：param   keep   切換   規模   hash   增加   重新命名   佔用   並且   

暫存資料表空間概念
暫存資料表空間用來管理資料庫排序操作以及用於儲存暫存資料表、中間排序結果等臨時對象,當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 FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME=‘DEFAULT_TEMP_TABLESPACE‘ 
SQL2：
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;
 
查看暫存資料表空間資訊：

SQL> SELECT BYTES,BLOCKS, USER_BYTES, USER_BLOCKS, BLOCKS-USER_BLOCKS AS SYSTEM_USED FROM DBA_TEMP_FILES; 
 
這四列中， 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; 

SQL> ALTER TABLESPACE TMPADD TEMPFILE ‘/u03/eps/oradata/temp02.dbf‘ SIZE 64G AUTOEXTEND OFF; 

刪除資料檔案
例如，我想刪除暫存資料表空間下的某個檔案，那麼我們有兩種方式刪除暫存資料表空間的資料檔案。
方法1：
SQL> ALTER TABLESPACE TEMP  DROP TEMPFILE ‘/u01/app/oracle/oradata/GSP/temp02.dbf‘;
注意：這種刪除暫存資料表空間的寫法會將對應的物理檔案刪除。
方法2：
SQL> ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/GSP/temp02.dbf‘  DROP INCLUDING DATAFILES; 
注意：刪除暫存資料表空間的臨時資料檔案時，不需要指定INCLUDING DATAFILES 選項也會真正刪除物理檔案。
 
調整檔案大小
如下例子，需要將臨時資料檔案從1G大小調整為2G
SQL> ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/GSP/temp02.dbf‘ RESIZE 2G; 
 
檔案離線聯機
SQL> ALTER DATABASE TEMPFILE2 ‘/u01/app/oracle/oradata/GSP/temp02.dbf‘ OFFLINE;

SQL> ALTER DATABASE TEMPFILE2 ‘/u01/app/oracle/oradata/GSP/temp02.dbf‘ ONLINE; 

預設暫存資料表空間並不能離線,否則會報錯，如下所示
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‘ AUTOEXTEND ON NEXT 100M 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  ‘/u01/app/oracle/oradata/GSP/temp4.dbf‘ TO  ‘/u01/app/oracle/oradata/GSP/temp04.dbf‘;
4: 將暫存資料表空間的臨時檔案聯機
SQL> ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/GSP/temp04.dbf‘ ONLINE; 
刪除暫存資料表空間
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
也可以指定已經建立好的暫存資料表空間的暫存資料表空間組。
SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP GRP_TEMP;

SQL> select * from dba_tablespace_groups;
從組中移除：
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 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;

 

oracle暫存資料表空間