以前寫了一篇ORACLE臨時表空間總結的文章， 裡面介紹了幾個查看臨時表空間使用情況的腳本，其中一個腳本如下所示：
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 GV_$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;
其實這個查看表空間的腳本是不准確的，有問題的（當然前面博客裡面所提到的腳本到現在也沒有改，以後也不打算修改了，就這樣放著吧）。你可以對比下面幾個腳本來看看。
如果臨時表空間是dictionary managed temporary tablespace，可以使用下面SQL：
SELECT (S.TOT_USED_BLOCKS/F.TOTAL_BLOCKS)*100 AS "PERCENT USED"
FROM
(SELECT SUM(USED_BLOCKS) TOT_USED_BLOCKS
FROM V$SORT_SEGMENT
WHERE TABLESPACE_NAME='TEMPSCM2'
) S,
(SELECT SUM(BLOCKS) TOTAL_BLOCKS
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME='TEMPSCM2'
) F;
如果臨時表空間是Locally Manageed Temporary Tablespace，使用下面SQL：
SQL> SELECT T.TABLESPACE_NAME,
( U.TOT_USED_BLOCKS / T.TOTAL_BLOCKS ) * 100 AS "PERCENT USED"
FROM (SELECT TABLESPACE_NAME,
SUM(USED_BLOCKS) TOT_USED_BLOCKS
FROM V$SORT_SEGMENT
WHERE TABLESPACE_NAME = &TABLESPACE_NAME
GROUP BY TABLESPACE_NAME) U,
(SELECT TABLESPACE_NAME,
SUM(BLOCKS) TOTAL_BLOCKS
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = &TABLESPACE_NAME
GROUP BY TABLESPACE_NAME) T;
當然你也可以使用下面SQL來查看臨時表空間的使用情況， 如下所示：
SELECT D.tablespace_name,
SPACE "SUM_SPACE(M)",
blocks "SUM_BLOCKS",
used_space "USED_SPACE(M)",
Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - used_space "FREE_SPACE(M)"
FROM (SELECT tablespace_name,
Round(SUM(bytes) / ( 1024 * 1024 ), 2) SPACE,
SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace,
Round(SUM(blocks * 8192) / ( 1024 * 1024 ), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+)
AND D.tablespace_name='TEMPSCM2'
那麼為什麼GV_$TEMP_SPACE_HEADER統計的數據不准確呢？ 這個是因為GV_$TEMP_SPACE_HEADER取數據不准確，官方解釋為：
The views v$sort_usage or v$tempseg_usage ( and v$sort_segment) give the correct information regarding the allocation of sort segments.
We should always query these views to find out the actual temp usage. The view v$temp_space_header shows that these many blocks were touched in each temp file at some point when temp usage was at its highest,
in essence, it shows the number of initialized blocks for each tempfile, not the actual allocated blocks.
The views v$sort_usage/v$tempseg_usage show the actual sort extents allocated for each transaction from these initialized blocks. Also, v$temp_space_header is persistent across restarts. V$sort_segment and v$sort_usage are not.
第二段我翻譯如下：
視圖v$temp_space_header顯示的是每一個temp文件在某一個時刻使用過的最大大小，從本質上說，它顯示的是每一個tempfile的初始化大小，而不是實際分配的塊大小。
所以說從視圖v$temp_space_header獲取的數據其實並不是實際使用的大小，它是不准確的。那麼肯定有人會問，腳本裡面不是訪問的GV_$TEMP_SPACE_HEADER視圖嗎? 跟這個視圖v$temp_space_header有關系嗎？ 答案是有關系，他們的數據來源是一致的，也就是說來自相同的內部表，如下所示：
一般來說，在GV$和V$之後， Oracle會建立GV_$和V_$視圖， 隨後為這些視圖建立了公用同義詞。GV_$TEMP_SPACE_HEADER是一個視圖，如下所示
SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME ='GV_$TEMP_SPACE_HEADER';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- ------------------------------- -------------------
SYS GV_$TEMP_SPACE_HEADER VIEW
GV_$TEMP_SPACE_HEADER視圖的定義如下所示：
SELECT DBMS_METADATA.GET_DDL('VIEW', 'GV_$TEMP_SPACE_HEADER', 'SYS') FROM DUAL;SELECT * FROM DBA_VIEWS WHERE VIEW_NAME='GV_$TEMP_SPACE_HEADER';
SELECT "INST_ID",
"TABLESPACE_NAME",
"FILE_ID",
"BYTES_USED",
"BLOCKS_USED",
"BYTES_FREE",
"BLOCKS_FREE",
"RELATIVE_FNO"
ROM gv$temp_space_header
而gv$temp_space_header視圖的定義如下（當然如果查詢DBA_OBJECTS會發現它是一個同義詞，指向GV_$TEMP_SPACE_HEADER,這個後面介紹原因)
SQL> select view_definition from v$fixed_view_definition
2 where view_name='GV$TEMP_SPACE_HEADER';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select /*+ ordered use_nl(hc) */ hc.inst_id, ts.name, hc.ktfthctfno, (hc.ktfthcs
z - hc.ktfthcfree)*ts.blocksize, (hc.ktfthcsz - hc.ktfthcfree), hc.ktfthcfree*ts
.blocksize, hc.ktfthcfree, hc.ktfthcfno from ts$ ts, x$ktfthc hc where ts.conten
ts$ = 1 and ts.bitmapped <> 0 and ts.online$ = 1 and ts.ts# = hc.ktfthctsn and h
c.ktfthccval = 0
SQL>
v$temp_space_header它也是一個視圖（查詢DBA_OBJECTS發現其是一個同義詞，這個後面介紹），你會發現v$temp_space_header其實是從視圖GV$TEMP_SPACE_HEADER過濾數據，如下所示：
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME =upper('v$temp_space_header');QL> select view_definition from v$fixed_view_definition
2 where view_name=upper('v$temp_space_header');IEW_DEFINITION
-------------------------------------------------------------------------------
elect TABLESPACE_NAME , FILE_ID , BYTES_USED , BLOCKS_USED , BYTES_FREE , BLOC
S_FREE , RELATIVE_FNO from GV$TEMP_SPACE_HEADER where inst_id = USERENV('Instane')
你在$ORACLE_HOME/rdbms/admin下的catspace.sql中，就會發現這樣的SQL，這就解釋了為什麼gv$temp_space_header是視圖，又是同義詞的原因。
create or replace view gv_$temp_space_header as select * from gv$temp_space_header;
create or replace public synonym gv$temp_space_header
for gv_$temp_space_header;
grant select on gv_$temp_space_header to SELECT_CATALOG_ROLE;
