Oracle 臨時表空間的概念

臨進表空間組:

臨進表空間組是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.

可以在建立臨時表空間是指定表空間組，即隱式建立。

SELECT * FROM DBA_TABLESPACE_GROUPS;

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

ALTER TABLESPACE  TEMP TABLESPACE GROUP GRP_TEMP;
select * from dba_tablespace_groups;

從組中移除：
ALTER TABLESPACE TEMP TABLESPACE GROUP '';
當為資料庫指定臨時表空間或為使用者指定臨時表空間時，可以使用臨時表空間組的名稱
ALTER USER DM TEMPORARY TABLESPACE GRP_TEMP;


監控臨時表空間


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;

################################################################

COL TEMP_FILE FOR A60;
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 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;