[OracleDB] 表領域変更SQL「alter tablespace」文とポイントを紹介

こんにちは！土門大貴(daikidomon)です。

Oracle Databaseの表領域の変更SQL「alter tablespace」文とポイントを紹介します。

単純な変更SQLは各種メディアやブログで紹介されていますが、この記事では表領域を変更する時の重要なポイントも合わせて紹介します。

この記事を読んで自動拡張有効化・オンライン/オフライン化・データファイルの追加などをマスターしてください。

表領域の変更SQL：alter tablespace

まずは表領域変更のSQLの基本構文を紹介します。

alter tablespaceは、既に存在している表領域を追加・削除・パラメータ変更・状態変更をさせるSQL文になります。
alter tablespace <表領域名> <表領域の変更操作>;

表領域のオンライン/オフラインの切り替え

表領域の操作コマンドである、表領域のオンライン/オフライン化の切り替えSQLを紹介します。

表領域のオンライン：alter tablespace <表領域名> online;

alter tablespace <表領域名> online;

表領域のバックアップやデータファイルの名前変更時に利用します。

↓サンプルSQLになります。
alter tablespace TESTTBS online;

表領域のオフライン：alter tablespace <表領域名> offline;

alter tablespace <表領域> offline;

表領域のバックアップやデータファイルの名前変更時に利用します。

↓がサンプルSQLになります。
alter tablespace TESTTBS offline;

表領域のデータファイルの追加・削除・名前変更方法

alter tablespace文と言えば、データファイルの追加・削除・名前変更方法になります。

それぞれの操作SQLを紹介します。

データファイルの追加：alter tablespace <表領域名> add datafile

alter tablespace <表領域>
add datafile <データファイル名> size <データファイルのサイズ>;
また表領域追加時には接尾語に番号を振ることをオススメします。

これはデータファイルを追加する時にファイル番号を振って管理しやすくするためです。

↓がサンプルSQLになります。
alter tablespace TESTTBS
add datafile '/u01/app/oracle/oradata/orcl/testtbs002.dbf' size 10m;
またデータファイルの追加時に、表領域作成時と同様にデータファイルに自動拡張有効化・無効化を指定できます。

下は自動拡張有効化設定をした時のSQLになります。
alter tablespace TESTTBS
add datafile '/u01/app/oracle/oradata/orcl/testtbs002.dbf' size 10m;
autoextend on next 1024k maxsize 1G;

データファイルの削除：alter tablespace <表領域名> drop datafile

alter tablespace <表領域名>
drop datafile <データファイル名>;
データファイルを削除するためデータが消える恐れがあります。

↓がサンプルSQLになります。
alter tablespace TESTTBS
drop datafile '/u01/app/oracle/oradata/orcl/testtbs002.dbf';

データファイルの名前変更：alter tablespace <表領域名> rename datafile

alter tablespace <表領域名>
rename datafile <変更前のデータファイル名>
to <変更後のデータファイル名>;

「alter tablespace <表領域名> rename datafile」では、OS上のファイル名を変更することができません。

そのため以下の手順が必要なります。

1. 表領域のオフライン「alter tablespace <表領域名> offline」
2. OS上のファイル変更
3. データファイル名の変更「alter tablespace <表領域名> rename datafile」
4. 表領域のオンライン「alter tablespace <表領域名> online」

↓がサンプルSQLになります。
-- 表領域のオフライン
alter tablespace TESTTBS offline;

-- OS上のファイル名を変更
!mv /u01/app/oracle/oradata/orcl/testtbs002.dbf /u01/app/oracle/oradata/orcl/testtbs003.dbf

-- データファイルの名前変更
alter tablespace TESTTBS
rename datafile '/u01/app/oracle/oradata/orcl/testtbs002.dbf'
to '/u01/app/oracle/oradata/orcl/testtbs003.dbf';

-- 表領域のオンライン
alter tablespace TESTTBS online;

表領域の自動拡張有効化・無効化

表領域の自動拡張有効化・無効化は2点注意があります。

• 表領域全体に自動拡張の有効化・無効化を設定する。
  データファイル毎に設定する場合は「alter database 」文で行う
• 「alter tablespace」で自動拡張有効化・無効化できるのは「bigfile表領域」のみ。
  smallfileだと「ORA-32773」が発生

自動拡張拡張の有効化：ALTER TABLESPACE <表領域名> AUTOEXTEND ON;

ALTER TABLESPACE <表領域名> AUTOEXTEND ON;

bigfile表領域のみ利用できるSQLになります。

↓がサンプルSQLになります。
ALTER TABLESPACE TESTTBS AUTOEXTEND ON;

自動拡張拡張の無効化：ALTER TABLESPACE <表領域名> AUTOEXTEND OFF;

ALTER TABLESPACE <表領域名> AUTOEXTEND OFF;
bigfile表領域のみ利用できるSQLになります。

↓がサンプルSQLになります。
ALTER TABLESPACE TESTTBS AUTOEXTEND OFF;
 

参考：しばちょう先生の試して納得！DBAへの道　第7回 表領域の管理方法を理解