Oracle 11g 新特性 -- 臨時表空間收縮(轉）(

一. 臨時表空間收縮

1.1 說明

關於Oracle 的臨時表空間，之前有整理過一篇Blog：

Oracle Temp 臨時表空間

http://blog.csdn.net/tianlesoftware/article/details/4697417

以下操作會佔用大量的temporary：

1、使用者執行imp/exp 匯入匯出操作時，會使用大量的temporary段

2、使用者在rebuild index時

3、執行create table ...... as 語句時

4、移動使用者下的資料到別的表空間時

大量的排序操作可能會導致臨時表空間大量增長。為了提高效能，對排序區進行物理分配後，將在記憶體中管理它們以避免以後的物理回收。結果，磁碟中包含一個巨大的臨時檔案，直到將其刪除。一種可能的解決方法是：使用較小的檔案建立新的臨時表空間，並將這個新的表空間設定為使用者的預設臨時表空間，然後刪除舊的表空間。但是，這有一個缺點，即過程要求刪除舊的臨時表空間時不能存在活動的排序操作。

從Oracle Database11g 版本1 開始，可使用ALTER TABLESPACESHRINK SPACE 命令收縮臨時表空間，也可以使用ALTER TABLESPACE SHRINKTEMPFILE 命令收縮臨時檔案。對於這兩個命令，可以指定可選的KEEP 子句，該子句定義了表空間/臨時檔案可收縮到的下限。

如果忽略KEEP 子句，則只要滿足其它儲存屬性，資料庫就會盡可能嘗試收縮表空間/臨時檔案（所有當前使用的區的總空間）。此操作需聯機執行。但是，如果所分配的當前使用的一些區超出了收縮估計值，系統將等待這些區被釋放以完成收縮操作。

注：

ALTER DATABASETEMPFILE RESIZE 命令通常會因ORA-03297 而失敗，因為臨時檔案包含的已用資料超過了所需的RESIZE 值。

與ALTER TABLESPACE SHRINK 相反，ALTER DATABASE 命令不會在排序區分配後嘗試取消分配。

在Oracle 11g 以前，Temp 表空間使用以後，雖然可以釋放，但是表空間的使用量顯示還是100%，可以使用如下指令碼檢視臨時表空間每個資料檔案實際使用量：

set pagesize 50

col tablespace_name for a20

col "Tempfile name" for a42

set linesize 300

Select f.tablespace_name,

d.file_name "Tempfile name",

round((f.bytes_free + f.bytes_used) / 1024 /1024, 2) "total MB",

round(((f.bytes_free + f.bytes_used) -nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" ,

round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2)"Used MB",

round((round(nvl(p.bytes_used, 0)/ 1024 /1024, 2)/round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))*100,2) as"Used_Rate(%)"

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;

1.2 DBA_TEMP_FREE_SPACE檢視

該字典檢視是在Oracle 11g新增加的檢視，用來檢視錶空間級別的臨時空間使用率資訊。此資訊是從各種現有檢視中匯出的。

(1) 列出臨時空間使用率資訊

(2) 臨時表空間使用率中心點

列名 說明

TABLESPACE_NAME 表空間的名稱

TABLESPACE_SIZE 表空間的總大小（以位元組為單位）

ALLOCATED_SPACE 已分配的總空間（以位元組為單位），包括當前已分配的且正在使用中的空間以及當前已分配的且可重用的空間

FREE_SPACE 可用的總空間（以位元組為單位），包括當前已分配的、可重用的以及當前未分配的空間

1.3 建立臨時表的表空間選項

從Oracle Database11g 版本1 開始，可以在建立全域性臨時表時指定TABLESPACE子句。

如果沒有指定表空間，將在預設的臨時表空間中建立全域性臨時表。此外，還會在與臨時表相同的臨時表空間中建立在臨時表中建立的索引。

注：

可以在DBA_TABLES 中查詢用於儲存全域性臨時表的表空間。

如：

CREATE TEMPORARY TABLESPACE temp

TEMPFILE 'tbs_temp.dbf' SIZE 600m REUSEAUTOEXTEND ON MAXSIZE

UNLIMITED

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m;

CREATE GLOBAL TEMPORARY TABLE temp_table (cvarchar2(10))

ON COMMIT DELETE ROWS TABLESPACE temp;

二．示例

2.1 檢視dba_temp_free_space

SQL> set lin 160

SQL> col tablespace_name for a20

SQL> col tablespace_size for 99999999999

SQL> col allocated_space for 99999999999

SQL> col free_space for 99999999999

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP 524288000 7340032 523239424

SQL> select 524288000/1024/1024||'M'from dual;

5242

----

500M

--這裡的Temp 表空間是500M。

2.2 執行temp 表空間的online shrink 操作：

SQL> alter tablespace temp shrink spacekeep 400M;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP 420478976 1048576 419430400

SQL> select 420478976/1024/1024||'M'from dual;

4204

----

401M

2.3 shrink 資料檔案

--如果有多個temp資料檔案，也可以直接指定某個特定的temp 資料檔案來進行shrink：

SQL> col file_name for a50

SQL> select file_name fromdba_temp_files;

FILE_NAME

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

/u01/app/oracle/oradata/anqing/temp01.dbf

SQL> alter tablespace temp shrinktempfile '/u01/app/oracle/oradata/anqing/temp01.dbf' keep 300M;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP 315613184 1040384 314572800

SQL> select 315613184/1024/1024||'M' from dual;

315613184/10

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

300.9921875M

SQL>

2.4 keep 選項說明

KEEP 選項用來指定壓縮時表空間或者資料檔案shrink的最小值，如果沒有執行該命令，那麼表空間或資料檔案將被壓縮到最小值。

SQL> alter tablespace temp shrink space;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP 2088960 1040384 1048576

SQL> select 2088960/1024/1024||'M' fromdual;

2088960/10

----------

1.9921875M

--這裡直接被壓到2M了。Temp 表空間過小對效能是有影響的，所以在shrink時，還是建議使用keep 指定最小值。