DBA_TABLESを基準にしてFUNCTIONやPROCEDUREのソースを調査し
そのテーブルが使用されているかどうかを検査するSQL。
このSQLの返却結果の中でOBJ_USING_COUNTが1以上のテーブルは
少なくともどこかで使われています。
⇒そのテーブルをDROPして削除する／ALTERして項目を削除する等すると
該当FUNCTION、PROCEFDUREがINVALID（コンパイルエラーの意）になる
DBA_SOURCE.TEXTにはPROCEDUREやFUNCTIONのソースコード１行１行が格納されていて、
DBA_SOURCE.LINEがその行数を管理しています。
このTEXTに調査用のテーブル名を部分一致で検索かけて、
使われている行があれば1、使われてなければ0を返却し、
それをDBA_TABLES.TABLE_NAME（とDBA_TAB_COMMENTS.COMMENTS）で集計するかんじです。
同じ名前のテーブルを別ユーザーで保持する場合は
DBA_TABLES.OWNERを集計項目に含めるか
そもそも調査対象のオブジェクトをDBA_TABLES⇒USER_TABLESに変更すればよいです。
ただし、このSQLはDBA_TABLESを基準にして
DB内の全テーブルを対象に検査するので結構時間がかかります。
調査したいのが特定のテーブルだとわかっているならWHERE句に
b.TABLE_NAME = 'AAA_TABLE'
とか
b.TABLE_NAME in ( 'AAA_TABLE' , 'BBB_TABLE')
みたいな１文を付け加えたほうが圧倒的に早いです。
要するにこのSQLは
全テーブルに対する現在の使用状況を調査する目的のものなので、
例えば「どこからも使用されてないもの」とか
「どこかしらで使用されているもの」を限定しようとしていません。
これらを限定する場合はWHERE句に
・どこからも使用されてないもの
upper(a.text) not like '%' || b.table_name || '%'
・どこかしらで使用されているもの
upper(a.text) like '%' || b.table_name || '%'
みたいな条件を付け加えればよいです。
ちなみに、FUNCTIONやPROCEDUREは起動時に自動コンパイルがかかるので、
本当にコンパイルエラーになるようなテーブルの変更をしない限り※は
わざわざ手動で再コンパイルかける必要もないみたいです。
※SELECT COLUMN1 INTO DATA_COLUMN1 FROM AAA_TABLE
みたいな記述があってAAA_TABLE自体をDROPする／COLUMN1をDROPする等して
そのロジック自体が死ぬ（動かなくなる）ような変更をしない限り、
という意味
私はなんとなく気持ち悪いから毎回再コンパイルかけてますが。
ちなみに全ソースを再コンパイルするやり方は
sqlplusにsysdba権限でログインしたあと
execute utl_recomp.recomp_serial();
とするだけ。
あと、このやり方はFUNCTIONやPROCEDUREに対する影響範囲の確認であって
MATERIALIZED VIEW（マテビュー）は含まれていません。
テーブルそのものを使用しているFUNCTIONやPROCEDUREはないけど
テーブルをもとにしたマテビューを使用しているFUNCTIONやPROCEDUREがたくさんある、
という場合は
テーブルの変更によりマテビューがIMVALIDに
↓
マテビューのINVALIDに従ってFUNCTIONやPROCEUDREがINVALIDに
という流れで、
直接影響が出ないように見えて実は影響が出てくるということもあります
というか実際ありました。
マテビューのソース情報はDBA_SOURCEには含まれていないため、
このやり方では（環境によっては）調査が不十分になります。
マテビューのソースはDBA_MVIEWS.QUERYに入ってるので、
調べるとしたらそこから調べることになりますが、
この項目はlong型なので調べるのにちょっと工夫がいります。
やり方としては
①sqlplusを起動
②set longchunksize 3000
③set long 2000000000
④SELECT QUERY FROM DBA_MVIEWS
としてspoolなりリダイレクトなりで結果を一度テキストファイルに出力して、
中身をgrepする方式が一番手っ取り早いそうです。
なお、上に挙げた方法だと
全マテビューのQUERYが同じテキストファイルに一遍に出力されるので、
作成されたテキストファイルにテーブル名でgrepしてひっかかっても
「どのマテビューで使ってんだ？」というのが区別つかなくなります。
※DBA_MVIEWS.QUERYには「CREATE MATERIALIZED VIEW ～～」の分は入っておらず
マテビューを定義するSELECTから始まるので絶対わけわかりません。
よって、マテビューが複数存在する環境では
実際にはDBA_MVIEWS.MVIEW_NAMEの数だけリストをまずつくり、
１マテビュー毎に④を実行して別々のファイルに出力するような考慮が必要になるでしょう。
といった感じでテーブルを使用しているオブジェクトの情報を検査できます。
マテビューが絡んでくると途端に面倒になるなというのが個人的な感想です。
今後のプロジェクトではできる限りマテビュー使いたくないです（個人的な意見）
