ここでは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