以前寫了一篇ORACLE臨時表空間總結(jié)的文章， 里面介紹了幾個查看臨時表空間使用情況的腳本，其中一個腳本如下所示：

?

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) TTWHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

?

其實(shí)這個查看表空間的腳本是不準(zhǔn)確的，有問題的（當(dāng)然前面博客里面所提到的腳本到現(xiàn)在也沒有改，以后也不打算修改了，就這樣放著吧）。你可以對比下面幾個腳本來看看。

???

如果臨時表空間是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;

?

當(dāng)然你也可以使用下面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統(tǒng)計的數(shù)據(jù)不準(zhǔn)確呢？ 這個是因?yàn)镚V_$TEMP_SPACE_HEADER取數(shù)據(jù)不準(zhǔn)確，官方解釋為：

?

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文件在某一個時刻使用過的最大大小，從本質(zhì)上說，它顯示的是每一個tempfile的初始化大小，而不是實(shí)際分配的塊大小。

?

所以說從視圖v$temp_space_header獲取的數(shù)據(jù)其實(shí)并不是實(shí)際使用的大小，它是不準(zhǔn)確的。那么肯定有人會問，腳本里面不是訪問的GV_$TEMP_SPACE_HEADER視圖嗎? 跟這個視圖v$temp_space_header有關(guān)系嗎？ 答案是有關(guān)系，他們的數(shù)據(jù)來源是一致的，也就是說來自相同的內(nèi)部表，如下所示：

?

一般來說，在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視圖的定義如下（當(dāng)然如果查詢DBA_OBJECTS會發(fā)現(xiàn)它是一個同義詞，指向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.ktfthcsz - hc.ktfthcfree)*ts.blocksize, (hc.ktfthcsz - hc.ktfthcfree), hc.ktfthcfree*ts.blocksize, hc.ktfthcfree, hc.ktfthcfno from ts$ ts, x$ktfthc hc where ts.contents$ = 1 and ts.bitmapped <> 0 and ts.online$ = 1 and ts.ts# = hc.ktfthctsn and hc.ktfthccval = 0??SQL>

?

?

v$temp_space_header它也是一個視圖（查詢DBA_OBJECTS發(fā)現(xiàn)其是一個同義詞，這個后面介紹），你會發(fā)現(xiàn)v$temp_space_header其實(shí)是從視圖GV$TEMP_SPACE_HEADER過濾數(shù)據(jù)，如下所示：

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 , BLOCS_FREE , RELATIVE_FNO from GV$TEMP_SPACE_HEADER where inst_id = USERENV('Instane')

?

你在$ORACLE_HOME/rdbms/admin下的catspace.sql中，就會發(fā)現(xiàn)這樣的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;

總結(jié)

以上是生活随笔為你收集整理的ORACLE使用GV_$TEMP_SPACE_HEADER统计临时表空使用情况不准确的问题的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。