【Oracleの便利SQL】一時表領域(TEMP)とテンポラリファイルのサイズと使用率を確認するSQL

本記事では、一時表領域(TEMP)とテンポラリファイルのサイズ、拡張サイズ、最大サイズを確認するSQLコマンドを紹介します。

一時表領域単位のサイズと使用率を確認

一時表領域(TEMP)のサイズ・割当済みサイズ・使用済みサイズ・空きサイズ・使用率を一覧で確認するSELECT文です。

set pages 300
set lines 300
col tablespace_name for a50
col NOW_SIZE[MB] for 9999999999
col ALLOCATED_SIZE[MB] for 9999999999
col FREE_SIZE[MB] for 9999999999
col USED_SIZE[MB] for 9999999999
col USED_PER for 999990.99
SELECT
	tablespace_name,
	tablespace_size/(1024*1024) as "NOW_SIZE[MB]",
	allocated_space/(1024*1024) as "ALLOCATED_SIZE[MB]",
	free_space/(1024*1024) as "FREE_SIZE[MB]",
	(tablespace_size-free_space)/(1024*1024) as "USED_SIZE[MB]",
	ROUND((tablespace_size-free_space)/tablespace_size * 100,2) as "USED_PER"
FROM
	dba_temp_free_space
;

出力されるカラムは以下のとおりです。

• TABLESPACE_NAME：表領域名
• NOW_SIZE[MB]：一時表領域全体のサイズ（MB）
• ALLOCATED_SIZE[MB]：割当済みサイズ（MB）
• FREE_SIZE[MB]：再利用可能な空きサイズ（MB）
• USED_SIZE[MB]：使用済みサイズ（NOW_SIZE − FREE_SIZE）（MB）
• USED_PER：使用率（％）

ALLOCATED_SPACE列は、現在割り当てられ、使用されている領域と、現在割り当てられ、再利用できる領域を含む、割り当てられた領域の合計量(バイト)です。
FREE_SPACE列は、現在割り当てられ、再利用できる領域と、現在割り当てられていない領域を含む、使用可能空き領域の合計量(バイト)です。
引用元：Oracle公式サイト

USED_SIZE[MB]列は、TABLESPACE_SIZE（一時表領域の全体サイズ）から、FREE_SPACE（再利用できる領域と未割り当て領域の合計サイズ）を引いて算出しています。

ALLOCATED_SPACEではなくFREE_SPACEを引いてから算出している理由は、ALLOCATED_SPACEには 「現在使用中の領域」だけでなく「過去に使用されて再利用可能になった領域」も含まれるため、 使用サイズの計算には適さないからです。

TABLESPACE_SIZEとALLOCATED_SPACE、FREE_SPACEの違いを式で表現すると、以下のようなイメージです。

• TABLESPACE_SIZE = 現在使用中の領域 + 再利用できる領域（過去に使用された領域）+ 未割り当て領域（未だ使用されたことのない領域）
• ALLOCATED_SPACE = 現在使用中の領域 + 再利用できる領域（過去に使用された領域）
• FREE_SPACE = 再利用できる領域（過去に使用された領域）+ 未割り当て領域（未だ使用されたことのない領域）

一時表領域の各テンポラリファイルのサイズを確認

テンポラリファイル(TEMPFILE)ごとに、現在サイズ・自動拡張設定・最大サイズを確認するSELECT文です。

一時表領域は永続表領域と異なり、データファイルではなくテンポラリファイルを使用するため、参照ビューもDBA_DATA_FILESではなくDBA_TEMP_FILESになります。

set pages 300
set lines 300
col tablespace_name for a50
col file_name for a80
col NOW_SIZE[MB] for 9999999999
col autoextensible for a3
col NEXT_SIZE[MB] for 9999999999
col MAX_SIZE[MB] for 9999999999
SELECT
	tablespace_name,
	file_name,
	bytes/(1024*1024) as "NOW_SIZE[MB]",
	autoextensible,
	(increment_by*(bytes/blocks))/(1024*1024) as "NEXT_SIZE[MB]",
	maxbytes/(1024*1024) as "MAX_SIZE[MB]"
FROM
	dba_temp_files
;

出力されるカラムは以下のとおりです。

• TABLESPACE_NAME：表領域名
• FILE_NAME：テンポラリファイル名（物理パス）
• NOW_SIZE[MB]：テンポラリファイルの現在サイズ（MB）
• AUTOEXTENSIBLE：自動拡張の有無（YES/NO）
• NEXT_SIZE[MB]：自動拡張時に追加されるサイズ（MB）
• MAX_SIZE[MB]：自動拡張時の最大サイズ（MB）

【Oracleの便利SQL】シリーズ記事