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

こんにちは！シミダイ(@shimidai2100)です。

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

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

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

表領域の変更SQL：alter tablespace

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

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

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

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

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

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

↓サンプルSQLになります。

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

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

↓がサンプルSQLになります。

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

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

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

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

また表領域追加時には接尾語に番号を振ることをオススメします。

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

↓がサンプルSQLになります。

またデータファイルの追加時に、表領域作成時と同様にデータファイルに自動拡張有効化・無効化を指定できます。

下は自動拡張有効化設定をした時のSQLになります。

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

データファイルを削除するためデータが消える恐れがあります。

↓がサンプルSQLになります。

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

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

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

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

↓がサンプルSQLになります。

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

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

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

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

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

↓がサンプルSQLになります。

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

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

↓がサンプルSQLになります。

 

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