Oracle Temp 資料表空間切換

標籤：mini   rda   rom   file   join   sort   lin   技術分享   運算   

一.TEMP資料表空間作用

 暫時資料表空間主要用途是在資料庫進行排序運算、管理索引、訪問視圖等操作時提供暫時的運算空間，當運算完畢之後系統會自己主動清理。當 oracle 裡須要用到 sort 的時候。 PGA 中 sort_area_size 大小不夠時，將會把資料放入暫時資料表空間裡進行排序，同一時候假設有異常情況的話。也會被放入暫時資料表空間 , 正常來說。在完畢 Select 語句、create index 等一些使用 TEMP 資料表空間的排序操作後， Oracle 是會自己主動釋放掉暫時段的。注意這裡的釋放，不過將這些空間標記為空白暇，並可重用，真正佔用的磁碟空間並沒有釋放。所以 Temp 資料表空間可能會越來越大。

排序是非常耗資源的。 Temp 資料表空間滿了 , 關鍵是最佳化你的語句，盡量使排序降低才是上策 .

總結為:

暫時資料表空間的主要作用：
  索引create或rebuild；
  Order by 或 group by；
  Distinct 操作。
  Union 或 intersect 或 minus。
  Sort-merge joins；
  analyze.

二、oracle temp資料表空間切換

2.1 查詢TEMP資料表空間使用方式:

SELECT temp_used.tablespace_name,
       total - used AS "Free",
       total AS "Total",
       ROUND (NVL (total - used, 0) * 100 / total, 3) "Free percent"
  FROM (  SELECT tablespace_name, SUM (bytes_used) / 1024 / 1024 used
            FROM GV$TEMP_SPACE_HEADER
        GROUP BY tablespace_name) temp_used,
       (  SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total
            FROM dba_temp_files
        GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name;

資料庫預設資料表空間:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
  2    FROM DATABASE_PROPERTIES
  3   WHERE PROPERTY_NAME = ‘DEFAULT_TEMP_TABLESPACE‘;

2.2 建立Temp2資料表空間

create temporary tablespace temp1 tempfile   ‘D:\APP\ADMINISTRATOR\ORADATA\SDXJ\TEMP2.dbf‘ size 20M autoextend on next 1M maxsize unlimited
tablespace group ‘‘
extent management local uniform size 1M;

2.3 改動資料庫預設資料表空間為Temp1

SQL> alter database default temporary tablespace temp1;
  資料庫已更改。

2.4 刪除原來資料表空間

退出session，刪除原資料表空間

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

刪除完畢。

三、Temp 資料表空間一些經常使用查詢

3.1、更改系統的預設暫時資料表空間：
      --查詢預設暫時資料表空間
    select * from database_properties where property_name=‘DEFAULT_TEMP_TABLESPACE‘;
     --改動預設暫時資料表空間
     alter database default temporary tablespace temp1;
      --全部使用者的預設暫時資料表空間都將切換為新的暫時資料表空間：
     select username,temporary_tablespace,default_ from dba_users;
      --更改某一使用者的暫時資料表空間：
     alter user scott temporary tablespace temp;

3.2 尋找消耗資源比較的sql語句
/* Formatted on 2015/7/14 21:58:17 (QP5 v5.163.1008.3004) */
  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

3.3、查看當前暫時資料表空間使用大小與正在佔用暫時資料表空間的sql語句
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  from v$sort_usage sort, v$session sess, v$sql sql
 where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sess.SQL_ADDRESS
 order by blocks desc;

4.4 對暫時資料表空間進行shrink（11g新增的功能）
--將temp資料表空間收縮為20M
alter tablespace temp1 shrink space keep 20M; 
--自己主動將資料表空間的暫時檔案縮小到最小可能的大小
ALTER TABLESPACE temp1 SHRINK TEMPFILE ’.../temp01.dbf’; 

總結: 至此Temp資料表空間維護完畢。

Oracle Temp 資料表空間切換