[Oracle] 一時表領域(temp tablespace)の再作成・縮小手順

2018年10月2日2022年6月4日

Oracle Databaseの一時表領域の再作成・縮小方法を紹介します。

一時表領域はソート処理やUNION処理で利用する大切な表領域になりますが、

「表領域サイズが拡大しすぎたり」、

「違う領域に移動したり」するケースもあります。

通常の表領域と再作成・縮小手順が違うので本記事を参考にしてみてください。

再作成・縮小手順の方針

再作成や縮小は直接行うことができません。

そのため一時的にdummy用の一時表領域を作成して、一時表領域を作成します。

以下が手順の流れになります。

ダミーの一時表領域を作成
1.で作成した一時表領域をデフォルト一時表領域に指定
対象の一時表領域を削除
一時表領域を作成
4.で作成した一時表領域をデフォルト表領域に指定
1.で作成したダミーの一時表領域を削除

再作成・縮小手順の手順：

まずはダミーの一時表領域を作成します。

一時表領域を作成するには

create temporary tablespace ～ tempfile

文を利用します。

create temporary tablespace dummy tempfile

'/u01/app/oracle/oradata/ORCL18C/dummy.dbf' size 10m autoextend on;

作成したダミーの一時表領域をデフォルト一時表領域に指定します。

デフォルト一時表領域のしては

alter database

文で実施します。

alter database default temporary tablespace dummy;

再作成・縮小対象の一時表領域を削除します。

削除は

drop tablespace

文で実施します。

drop tablespace TEMP including contents and datafiles;

一時表領域を作成します。

create temporary tablespace TEMP tempfile

'/u01/app/oracle/oradata/ORCL18C/temp01.dbf' size 50m autoextend on;

作成した一時表領域をデフォルト表領域に指定します。

alter database default temporary tablespace TEMP;

1.で作成したダミーの一時表領域を削除します。

drop tablespace dummy including contents and datafiles;

補足：一時表領域の確認SQL

今回の手順で実施した確認用SQLもあわせて紹介します。

一時表領域のデータファイルとサイズを確認

一時表領域のデータファイルとサイズ確認する時は、

dba_temp_files

ビューを確認します。

set linesize 300;
set pagesize 100;
col tablespace_name for a10
col file_name for a50
select tablespace_name, file_name, bytes/1024/1024 as "SIZE(MB)"
from dba_temp_files order by file_name;
以下が実行例になります。
17:15:51 SYS@orcl18c > set linesize 300;
17:16:08 SYS@orcl18c > set pagesize 100;
17:16:08 SYS@orcl18c > col tablespace_name for a10
17:16:08 SYS@orcl18c > col file_name for a50
17:16:08 SYS@orcl18c > select tablespace_name, file_name, bytes/1024/1024 as "SIZE(MB)"
17:16:08 2 from dba_temp_files order by file_name;

TABLESPACE FILE_NAME SIZE(MB)
---------- -------------------------------------------------- ----------
DUMMY /u01/app/oracle/oradata/ORCL18C/dummy.dbf 10
TEMP /u01/app/oracle/oradata/ORCL18C/temp01.dbf 50

デフォルト一時表領域の確認

デフォルト一時表領域を確認する時は、

database_properties

ビューを確認します。

set linesize 300; set pagesize 100; col property_name for a30; col property_value for a35; select property_name, property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

以下実行例になります。

17:13:39 SYS@orcl18c > set linesize 300;
17:14:22 SYS@orcl18c > set pagesize 100;
17:14:22 SYS@orcl18c > col property_name for a30;
17:14:22 SYS@orcl18c > col property_value for a35;
17:14:22 SYS@orcl18c > select property_name, property_value
17:14:22 2 from database_properties
17:14:22 3 where property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ -----------------------------------
DEFAULT_TEMP_TABLESPACE TEMP