一時表領域の最大／使用サイズを確認するスクリプト

DBA_TEMP_FILES 及び V$TEMP_SPACE_HEADER を参照する。

SET LINESIZE 170;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
COLUMN MAX_BYTES  FORMAT 999,999,999,999;
COLUMN FREE_BYTES FORMAT 999,999,999,999;
COLUMN USED_BYTES FORMAT 999,999,999,999;
SELECT SYSDATE
     , T.TABLESPACE_NAME
     , T.MAX_BYTES
     , TF.FREE_BYTES
     , T.MAX_BYTES - TF.FREE_BYTES AS USED_BYTES
  FROM (
    SELECT TABLESPACE_NAME AS TABLESPACE_NAME
         , SUM(BYTES)      AS MAX_BYTES
      FROM DBA_TEMP_FILES
     GROUP BY TABLESPACE_NAME
  ) T ,
  (
    SELECT TABLESPACE_NAME AS TABLESPACE_NAME
         , SUM(BYTES_FREE) AS FREE_BYTES
      FROM V$TEMP_SPACE_HEADER TF
     GROUP BY TABLESPACE_NAME
  ) TF
 WHERE T.TABLESPACE_NAME = TF.TABLESPACE_NAME(+);

結果のサンプルは↓

SYSDATE             TABLESPACE_NAME     MAX_BYTES    FREE_BYTES    USED_BYTES
------------------- --------------- ------------- ------------- -------------
2011/11/17 01:49:14 TEMP            1,073,741,824   610,271,232   463,470,592