連結：http://blog.itpub.net/28602568/viewspace-1770577/

前言：

之前簡單寫過"Oracle 查詢各個 “表空間/資料檔案” 的空間使用比情況",當時只簡單用到dba_data_file"總大小"與dba_free_space"剩餘大小"來判斷空間使用情況；

但當如下2種情況產生的時候 ,以前的指令碼不太適用；

1、 DBF設定AUTOEXTENSIBLE='YES'自增長時, 例如：初步分配10G,使用9.9G,原方法使用率 = 9.9/ 10 *100 = 99%,而實際為=9.9 /總( 'smailfile' ≈32 G )*100 = 31%；

2、 UNDO表空間迴圈使用,當大部分空間分配狀態為UNEXPIRED,EXPIRED時,按照之前方式所算的使用率完全沒有參考價值；

針對如上的2中情況 ,在如下sql已做改進.

SQL :

SELECT TBSP.TABLESPACE_NAME ,

TBSP.TOTAL_GB ,

TBSP.USE_GB ,

TBSP.TOTAL_GB - TBSP.USE_GB FREE_GB,

ROUND (TBSP.USE_GB / TBSP.TOTAL_GB * 100 ,2 ) "USE%"

FROM ( SELECT TOTAL.TABLESPACE_NAME ,

TOTAL_GB ,

/*USE.TABLESPACE_NAME IS NULL 表示表空間使用為0'未分配段'*/

( CASE WHEN USE.TABLESPACE_NAME IS NULL THEN 0

/*各UNDO表空間ACTIVE狀態USE_GB

--ACTIVE表示目前仍活躍的事務相關回滾資訊；

--UNEXPIRED表示雖然事務已經結束但回滾資訊保留的時間仍未超過例項引數 UNDO_RETENTION所設定的值；

--EXPIRED表示回滾資訊保留時間已超過UNDO_RETENTION所設定的值*/

WHEN TOTAL.TABLESPACE_NAME IN ( SELECT DISTINCT TABLESPACE_NAME FROM DBA_UNDO_EXTENTS) THEN

( SELECT ROUND(NVL(SUM(BLOCKS) * 8 / 1024 / 1024,0), 2) AS "SIZE G" FROM DBA_UNDO_EXTENTS WHERE STATUS = 'ACTIVE'

AND TABLESPACE_NAME = TOTAL.TABLESPACE_NAME) ELSE USE_GB END ) USE_GB

FROM (/*表空間總大小*/

SELECT TABLESPACE_NAME ,

/*自動增長，OS層空間足夠時，取MAXBYTES*/

ROUND (SUM (( CASE WHEN AUTOEXTENSIBLE = 'YES' THEN MAXBYTES ELSE BYTES END ) / 1024 / 1024 / 1024 ), 2 ) TOTAL_GB

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) TOTAL

LEFT JOIN ( /*表空間真實使用情況*/

SELECT TABLESPACE_NAME ,

ROUND (SUM ( BYTES / 1024 / 1024 / 1024), 2 ) USE_GB

FROM DBA_SEGMENTS

GROUP BY TABLESPACE_NAME) USE

ON USE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME) TBSP

UNION ALL ( /*臨時表空間使用情況*/

SELECT TABLESPACE_NAME ,

ROUND (TF.TABLESPACE_SIZE / 1024 / 1024 / 1024 , 2) TABLESPACE_NAME ,

ROUND ((TF.TABLESPACE_SIZE - TF.FREE_SPACE) / 1024 / 1024 / 1024 , 2 ) USE_GB ,

ROUND (TF.FREE_SPACE / 1024 / 1024 / 1024 , 2) FREE_GB ,

ROUND ((TF.TABLESPACE_SIZE - TF.FREE_SPACE) /TF.TABLESPACE_SIZE * 100 , 2) "USE%"

FROM DBA_TEMP_FREE_SPACE TF);

檢視官方介紹：

DBA_DATA_FILES style="font-size:10px;">

DBA_SEGMENTS style="font-size:10px;">

DBA_TEMP_FREE_SPACE style="font-size:10px;">

DBA_UNDO_EXTENTS style="font-size:10px;">

提示：

若想針對各個DBF使用率進行查詢，需要完善例如DBA_SEGMENTS.HEADER_FILE = DBA_DATA_FILES.FILE_ID...等

【源於本人筆記】若有書寫錯誤，表達錯誤，請指正...

此條目發表在 SQL、SQL最佳化篇分類目錄。將固定連線加入收藏夾。

Oracle 查詢各表空間使用情況--完善篇

相關文章