ORALCE 臨時表空間

一 臨時表空間概念

臨時表空間用來管理資料庫排序操作以及用於儲存臨時表、中間排序結果等臨時物件,當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。

二臨時表空間資訊

1 檢視系統預設臨時表空間(database_properties)

SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

2 檢視使用者臨時表空間(dba_users)

SELECT USERNAME, default_tablespace,TEMPORARY_TABLESPACE FROM DBA_USERS where username='CHEN';

3 檢視臨時表空間詳細資訊(v$tempfile,dba_temp_files)

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;

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;

select a.tablespace_name,

a.file_name,

a.status,

b.status as bstatus,

a.autoextensible,

a.increment_by,

a.bytes / 1024 / 1024 / 1024 || 'G' as file_size,

decode(a.maxbytes,

0,

a.bytes / 1024 / 1024 / 1024 || 'G',

maxbytes / 1024 / 1024 / 1024 || 'G') as max_size,

a.USER_BYTES / 1024 / 1024 / 1024 || 'G' AS "USEFUL_SIZE"

from dba_temp_files a, v$tempfile b

where a.file_name = b.name;

其中使用decode函式的原因：

當臨時表空間不是自動增長時，maxbytes=bytes

當臨時表空間是自動增長時,maxbytes=maxbytes

三 管理臨時表空間

1 建立臨時表空間

create temporary tablespace chen_temp tempfile '/data/oradata/cts/chen_temp01.dbf' size 100M autoextend off;

2 增加資料檔案

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

方法一：

alter tablespace chen_temp add tempfile '/data/oradata/cts/chen_temp02.dbf' size 50m autoextend on next 10M maxsize 100M;

方法二：

alter tablespace chen_temp add tempfile '/data/oradata/cts/chen_temp03.dbf' size 50M autoextend off;

3 調整檔案大小

如下例子，需要將臨時資料檔案從50M大小調整為60M

alter database tempfile '/data/oradata/cts/chen_temp03.dbf' resize 60M;

4 設定檔案自動擴充套件, 取消自動增長

alter database tempfile '/data/oradata/cts/chen_temp03.dbf' autoextend on next 10M maxsize unlimited;

alter database tempfile '/data/oradata/cts/chen_temp03.dbf' autoextend off;

5 收縮臨時表空間

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

alter tablespace chen_temp shrink space keep 200M;

alter tablespace chen_temp shrink tempfile '/data/oradata/cts/chen_temp03.dbf';

6 切換臨時表空間

1 切換系統預設臨時表空間

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

alter database default temporary tablespace chen_temp;

2 切換使用者預設來臨時表空間

select username,temporary_tablespace from dba_users where username='CHEN';

alter user chen temporary tablespace chen_temp;

7 移動重新命名檔案

例如，我需要將/data/oradata/cts/chen_temp03.dbf這個檔案移動並重新命名為/data/oradata/cts/chen/chen_temp003.dbf

1: 將臨時表空間的臨時檔案離線

alter database tempfile '/data/oradata/cts/chen_temp03.dbf' offline;

2：移動或重新命名相關的臨時檔案

mv /data/oradata/cts/chen_temp03.dbf /data/oradata/cts/chen/chen_temp003.dbf

3: 使用指令碼ALTER DATABASE RENAME FILE

alter database rename file '/data/oradata/cts/chen_temp03.dbf' to '/data/oradata/cts/chen/chen_temp003.dbf';

4: 將臨時表空間的臨時檔案聯機

alter database tempfile '/data/oradata/cts/chen/chen_temp003.dbf' online;

8 監控臨時表空間

1：檢視臨時表空間使用情況

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 V$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;

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;

2：檢視臨時表空間對應的臨時檔案的使用情況

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;

3 查詢消耗臨時表空間資源比較多的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;

9 刪除臨時表空間及資料檔案

(1)刪除資料檔案

方法1：

alter tablespace chen_temp drop tempfile '/data/oradata/cts/chen_temp01.dbf';

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

方法2：

alter database tempfile '/data/oradata/cts/chen_temp02.dbf' drop including datafiles;

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

(2)刪除臨時表空間

drop tablespace chen_temp including contents and datafiles cascade constraints;

注意：

1 執行上面語句很長時間沒沒有結束，查詢SELECT * FROM v$sort_usage where tablespace='CHEN_TEMP';

發現有 大量的會話在使用這個空間，所有無法刪除。

解決辦法：重啟資料庫alter system checkpoint;

Shutdown immediate

Startup

在查詢SELECT * FROM v$sort_usage where tablespace='CHEN_TEMP';已經沒有會話；

再次執行drop tablespace chen_temp including contents and datafiles cascade constraints;成功。

2 Drop table cascade constraints 是不影響到儲存於object裡的row data,只是刪除了相應的約束條件。

3 不能刪除當前使用者的預設表空間，否則會報ORA-12906錯誤。

如果需要刪除某一個預設的臨時表空間，則必須先建立一個臨時表空間，然後指定新建立的表空間為預設表空間，然後刪除原來的臨時表空間。

四 臨時表空間組

Oracle 10g之前，同一使用者的多個會話只可以使用同一個臨時表空間，因為在給定的時間只有一個臨時表空間預設給使用者，為了解決這個潛在的瓶頸，Oracle支援臨時表空間組即包含多個臨時表空間的集合。臨時表空間組邏輯上就相當於一個臨時表空間。

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

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

create temporary tablespace chen_temp1 tempfile '/data/oradata/cts/chen_temp01.dbf' size 10M;

create temporary tablespace chen_temp2 tempfile '/data/oradata/cts/chen_temp02.dbf' size 10M;

create temporary tablespace chen_temp3 tempfile '/data/oradata/cts/chen_temp03.dbf' size 10M;

新增chen_temp1，chen_temp2，chen_temp3到臨時表空間組tempgrp中

alter tablespace chen_temp1 tablespace group tempgrp;

alter tablespace chen_temp2 tablespace group tempgrp;

alter tablespace chen_temp3 tablespace group tempgrp;

啟用臨時表空間組

alter database default temporary tablespace tempgrp;

select * from dba_tablespace_groups;

此時資料庫所有使用者的預設臨時表空間為tempgrp

SELECT USERNAME, default_tablespace,TEMPORARY_TABLESPACE FROM DBA_USERS where username='CHEN';

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

alter tablespace chen_temp1 tablespace group tempgrp;

刪除臨時表空間組

必須先刪除成員

alter tablespace chen_temp1 tablespace group '';

alter tablespace chen_temp2 tablespace group '';

select * from dba_tablespace_groups;

當表空間組是資料庫預設表空間時，最後一個成員刪除報錯：ORA-10919：Defualt temporary tablespace group must be have at least one tablespace

此時再刪除最後一個成員，臨時表空間組自動消失

alter tablespace chen_temp3 tablespace group '';

select * from dba_tablespace_groups;

刪除臨時表空間

SELECT * FROM v$sort_usage where tablespace='CHEN_TEMP1';

SELECT * FROM v$sort_usage where tablespace='CHEN_TEMP2';

SELECT * FROM v$sort_usage where tablespace='CHEN_TEMP3';

drop tablespace chen_temp1 including contents and datafiles cascade constraints;

drop tablespace chen_temp2 including contents and datafiles cascade constraints;

drop tablespace chen_temp3 including contents and datafiles cascade constraints;

參考：

http://www.cnblogs.com/david-zhang-index/archive/2012/04/11/2442726.html

http://www.cnblogs.com/kerrycode/p/4006840.html

oracle臨時表空間

相關文章