Oracle 11G清理暫存資料表空間____Oracle

一. 暫存資料表空間收縮

  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 指定最小值。

轉載於：http://blog.csdn.net/tianlesoftware/article/details/8225395