Edited at

OracleDBでDROP TABLEするときに確認すること

More than 1 year has passed since last update.


はじめに

最近OracleDBでDROP TABLEしたあと再作成することがあったので、その際に必要だった「DROPする予定のTABLEに関係あるオブジェクト」を確認するSQL文を残しておきたいと思います。なお、オブジェクトの中でもINDEXと整合性制約についてはCREATE文の中に入れるのが一般的だと思うので無視しています。すみません。

なぜ必要かなのですが、DROP TABLEした際にTABLEが所有しているオブジェクトが消えちゃったり、TABLEを参照しているオブジェクトがINVALID(無効)になってしまうためです。

(ちなみにDROP TABLEしたあとに再作成とか何考えてんねんって思われる方もいるかもしれないですが、非パーティショニング表をパーティショニング表にしたかったためです。しかしALTER TABLEでできるような気はしているのでそのうちまた調べます。。。。)


TABLEに関係するオブジェクト

TABLEに関係するオブジェクトは以下です。


  • TRIGGER

  • SYNONYM

  • SEQUENCE

  • PROCEDURE

  • FUNCTION

  • VIEW

  • PACKAGE

この中で、特に気をつけるべきなのはTRIGGERみたいです。

他のオブジェクトはDROP TABLEした際にSTATUSがINVALIDになるだけですが、TRIGGERはTABLEに紐づいているので消えてしまうみたいです。(同様にINDEXと整合性制約も消えてしまいます。これは直感的なのでわかりやすいですね。)


TABLEに紐づいているTRIGGERを確認する

ではDROPする予定のTABLEに紐づいているTRIGGERを確認しましょう。

SELECT TRIGGER_NAME, TABLE_NAME, STATUS

 FROM USER_TRIGGERS
 WHERE TABLE_NAME = 'テーブル名''

解説するまでもありませんが、ここでのTABLE_NAMEとはTRIGGERを所有しているTABLEの名前のことです。

DROP TABLEしてしまうと再作成してもTRIGGERは削除されたままになってしまうので何とかしておかないといけませんね。(CREATE TRIGGER文をバックアップしておいて後でTRIGGERも再作成が一般的なのかも。)

ちなみにSYNONYMのUSERTABLEもあるのでそれで確認することもできます。

SELECT SYNONYM_NAME, TABLE_NAME

 FROM USER_SYNONYMS
 WHERE TABLE_NAME = 'テーブル名'


TABLEを参照しているオブジェクトを確認する

TABLEを参照しているオブジェクトを確認するためにはUSER_DEPENDENCIESや、ALL_DEPENDENCIES、DBA_DEPENDENCIESを使います。

SELECT NAME, TYPE, REFERENCED_NAME, 

 FROM DBA_DEPENDENCIES
 WHERE REFERENCED_NAME = 'テーブル名'
 AND(TYPE = 'TRIGGER'
 OR TYPE = 'SEQUENCE'
 OR TYPE = 'PROCEDURE'
 OR TYPE = 'FUNCTION'
 OR TYPE = 'VIEW'
 OR TYPE = 'PACKAGE BODY')

REFERENCED_NAMEがその名の通り参照されているオブジェクト名です。

ここで、検索条件のTYPEにTRIGGERをいれている理由は、TABLEに紐づいてはいないけど参照している場合があるからです。その場合はDROP TABLEと同時に削除はされずにSTATUSがINVALIDになります。

なお、もちろんこのSQL文でSYNONYMを確認することもできます(上のSQL文の存在意義はありません)。

このSQL文で抽出したオブジェクトはSTATUSがINVALIDになるため、DROP TABLE⇒再作成後にリコンパイルが必要になります。

(なお、DROP TABLEだけで再作成しない場合はこれらのオブジェクトを書き換える必要があるかもしれません)