Oracleの表・表領域の削除まとめ。drop table・drop tablespaceの構文とUNDO表領域の再作成

Oracle Databaseの表・表領域の削除SQLを、この記事1本にまとめて解説します。

• テーブルの削除: drop table（purge・cascade constraint句）
• 表領域の削除: drop tablespace（including contents・and datafiles句）
• UNDO表領域の再作成: 一時UNDO表領域への切り替えを使った安全な手順

なお、ユーザーの削除（drop user）はユーザー管理まとめ、インデックスの削除（drop index）はインデックス管理まとめを参照してください。

テーブルの削除：drop table

テーブル削除には「定義は削除するがデータをゴミ箱に残すか」「データも定義も完全に削除するか」といったパターンがあります。

基本構文

drop table TEST001.TAB001;

この形式では、テーブルはRECYCLEBIN（Windowsのゴミ箱に相当）へ移動され、flashback table で復元できます。

データを完全に削除する：purge

drop table TEST001.TAB001 purge;

purge 句を付けるとRECYCLEBINを経由せず完全に削除されます（復元不可）。なお、RECYCLEBINがOFFの環境では purge を付けなくても完全に削除されます。

制約も合わせて削除する：cascade constraints

drop table TEST001.TAB001 cascade constraints;

他のテーブルから外部キーで参照されているテーブルは、cascade constraints 句がないと削除できません。

データも定義も制約もすべて削除する

drop table TEST001.TAB001 cascade constraints purge;

表領域の削除：drop tablespace

基本構文

drop tablespace testtbs;

何も指定しない場合、Oracle上から論理的に削除されるだけで、OS上のデータファイルは削除されません。また、オブジェクトが含まれている表領域では「ORA-01549: 表領域が空ではありません」が発生します。

中のオブジェクトも削除する：including contents

drop tablespace testtbs
including contents;

OS上のデータファイルも削除する：and datafiles

drop tablespace testtbs
including contents
and datafiles;

and datafiles は including contents とセットで指定する必要があります。単独で指定すると「ORA-02173: DROP TABLESPACEのオプションが無効です」が発生します。

参照整合性制約も削除する：cascade constraints

表領域外のテーブルから、この表領域内の主キー・一意キーを参照する整合性制約がある場合に指定します。こちらも including contents とセットで指定します。

完全削除の形

drop tablespace testtbs
including contents
and datafiles
cascade constraints;

UNDO表領域の再作成

「誤った設定でUNDO表領域を作成してしまった」「UNDO表領域が肥大化したので小さくしたい」場合、一時的に別のUNDO表領域へ切り替えている間にDROP/CREATEする方法で安全に再作成できます。

手順の流れ:

1. 一時的なUNDO表領域を作成
2. 初期化パラメータ UNDO_TABLESPACE を一時UNDO表領域へ切り替え
3. 元のUNDO表領域をオフライン化
4. 元のUNDO表領域を削除
5. UNDO表領域を再作成
6. UNDO_TABLESPACE を再作成した表領域へ戻す
7. 一時UNDO表領域を削除

実行手順

一時的なUNDO表領域を作成し、切り替えます。

create undo tablespace undotemp datafile '/u01/app/oracle/oradata/ORCL18C/undotemp01.dbf' size 1g;
alter system set undo_tablespace = 'UNDOTEMP';
show parameters undo_tablespace

元のUNDO表領域をオフラインにして削除します。

alter tablespace undotbs1 offline;
select status from dba_tablespaces where tablespace_name = 'UNDOTBS1';

drop tablespace undotbs1 including contents and datafiles cascade constraints;

UNDO表領域を望みのサイズで再作成し、切り戻します。

create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/ORCL18C/undotbs101.dbf' size 10480m autoextend off;
alter system set undo_tablespace = 'UNDOTBS1';
show parameters undo_tablespace

最後に一時UNDO表領域を削除します。

drop tablespace undotemp including contents and datafiles cascade constraints;

各ステップで dba_data_files を確認しながら進めると安全です。

select * from dba_data_files where tablespace_name = 'UNDOTBS1';

まとめ

操作	SQL	注意点
テーブル削除	drop table ~	RECYCLEBINに残る（flashbackで復元可）
テーブル完全削除	drop table ~ purge	復元不可
外部キー参照ありの削除	drop table ~ cascade constraints	参照制約も削除される
表領域削除	drop tablespace ~ including contents and datafiles	and datafilesを忘れるとOSファイルが残る
UNDO再作成	一時UNDOへ切り替えてDROP/CREATE	切り戻しと一時UNDOの削除を忘れずに

参考: しばちょう先生の試して納得！DBAへの道「表と表領域の関係」「表領域の管理方法を理解」