ここではORACLEデータベースで、表領域を作成・変更・サイズ拡張・削除するSQLを紹介しています。

表領域の一覧や使用率を確認する方法は↓で紹介しています。
＞＞【ORACLE】表領域の一覧や使用率を確認するSQL

表領域とは

表領域とは、テーブルやインデックスを格納する領域です。

表領域は1つ以上のデータファイルで出来ています。逆に1つのデータファイルが複数の表領域に紐づくことは出来ません。

表領域を作成するSQL

表領域の作成はCREATE TABLE文を使います。

表領域の名前は最大30バイトです。

表領域の作成や変更、削除には↓の権限が必要です。
　作成：CREATE TABLESPACE
　変更：ALTER TABLESPACE
　削除：DROP TABLESPACE

システム権限の付与の方法については↓で紹介していますので参考にしてください。
＞＞【ORACLE】システム権限を付与・取消するSQL

CREATE TABLESPACE文の基本構文

CREATE TABLESPACE文の基本構文は次の通りです。

CREATE [BIGFILE | SMALLFILE] TABLESPACE 表領域名
 [DATAFILE データファイル名 SIZE サイズ]
 [AUTONEXTEND {OFF | ON [NEXT サイズ] [MAXSIZE サイズ]}]
 [MINIMUM EXTENT サイズ]
 [BLOCKSIZE サイズ]
 [LOGGING | NOLOGGING]
 [ONLINE | OFFLINE]
 [PERMANENT | TEMPORARY]
;

・BIGFILE/SMALLFILE
　BIGFILEかSMALLFILEが指定します。データベースのデフォルトと違う種類を指定するときに指定します。
・DATAFILE
　データファイル名とサイズを指定します。
・AUTONEXTEND
　データファイルの自動拡張を指定します。
　OFF：自動拡張しない
　ON：自動拡張する、NEXTで自動拡張するサイズ、MAXSIZEで拡張サイズの最大を指定できます。MAXSIZEでUNLIMITEDを指定すると制限なしにできます。
・MINIMUM EXTENT
　エクステントの最小サイズを指定します。
・BLOCKSIZE
　ブロックサイズを指定します。指定しない場合はデフォルトのブロックサイズが適用されます。
・LOGGING/NOLOGGING
　表領域に対する操作をREDOログに出力するかどうか指定します。デフォルトはLOGGINGです。
・ONLINE/OFFLINE
　ONLINEを指定することで表領域を使用可能にします。OFFLINEで使用禁止にします。デフォルトはONLINEです。
・PERMANENT/TEMPORARY
　永続表領域にする場合はPERMANAENT、一時表領域にする場合はTEMPORARYを指定します。デフォルトはPERMANAENTです。

CREATE TABLESPACE文を使ったSQLサンプル

CREATE TABLESPACE文のサンプルです。

次のSQLでは、TBS1表領域をDF1.DBFで作成しています。自動拡張の設定で増分1M、最大サイズ無制限にしています。

CREATE TABLESPACE tbs1
DATAFILE 'c:\ORACLE\DATA\df1.dbf' SIZE 100M
AUTONEXTEND ON NEXT 1M MAXSIZE UNLIMITED
;

複数のデータファイルで表領域を作成するSQL

CREATE TABLESPACE tbs1 DATAFILE 'c:\ORACLE\DATA\dbf1.dbf' SIZE 100M,
                                'c:\ORACLE\DATA\dbf2.dbf' SIZE 100M,
                                'c:\ORACLE\DATA\dbf3.dbf' SIZE 100M;

BIGFILEで表領域を作成するSQL

CREATE BIGFILE TABLESPACE tbs1
DATAFILE 'c:\ORACLE\DATA\df1.dbf' SIZE 100M
AUTONEXTEND ON NEXT 1M MAXSIZE UNLIMITED
;

表領域を変更するSQL

ALTER文を使うことで、表領域を変更することが出来ます。

表領域の名前を変更するSQL

ALTER TABLESPACE tbs1 RENAME TO tbs2;

表領域を読取専用に変更するSQL

ALTER TABLESPACE tbs1 READ ONLY;

表領域をONLINE/OFFLINEに変更するSQL

ALTER TABLESPACE tbs1 ONLINE;
ALTER TABLESPACE tbs1 OFFLINE;

表領域にデータファイルを追加するSQL

ALTER TABLESPACE tbs1 ADD DATAFILE 'c:\ORACLE\DATA\df2.dbf' SIZE 100M;

サイズの拡張

表領域のサイズ拡張と縮小

ALTER TABLESPACE tbs1 DATAFILE 'c:\ORACLE\DATA\df1.dbf' SIZE 100M;

データファイルのサイズ拡張と縮小

--データファイルサイズの確認
select a.FILE_NAME,a.TABLESPACE_NAME,a.BYTES from DBA_DATA_FILES a；

--データファイルサイズの拡張・縮小
alter database datafile '/oracle/oradata/xxDB/data/user1.dbf' resize 100M;

表領域を削除するSQL

DROP文を使うことで表領域を削除することが出来ます。

DROP TABLESPACE 表領域名
[INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]];

・INCLUDING CONTENTS
　表領域内のオブジェクトも同時に削除します。
・AND DATAFILES
　表領域と同時にデータファイルも削除します。
・CASCADE CONSTRAINTS
　表領域と同時に参照整合性制約も同時に削除します。

DROP TABLESPACE文を使ったSQLサンプル

次のSQLでは、表領域の削除と同時に、オブジェクトとデータファイルを削除しています。

DROP TABLESPACE tbs1 INCLUDING CONTENTS AND DATAFILES;

まとめ

表領域の作成はCREATE TABLESPACE、変更はALTER文、削除はDROP文を使います。

表領域の作成や変更、削除には↓の権限が必要です。
　作成：CREATE TABLESPACE
　変更：ALTER TABLESPACE
　削除：DROP TABLESPACE

システム権限の付与の方法については↓で紹介していますので参考にしてください。
＞＞【ORACLE】システム権限を付与・取消するSQL