DBからレコードを削除したときのエビデンスの残し方

Question

DBから200万件のレコードを削除しなくてはいけません。
削除対象が日々変わっているため事前調査もあまり意味がない状態です。
どのようにエビデンスを残せばよいのでしょうか。

今までは100件程度だったので以下の手順で確認及びエビデンスを残していました。

①エクセルに関数で左右のレコードが同じならTRUEを返すシートを作成しておく。
↓
②事前調査で対象のデータを抽出
↓
③エクセルに貼り付け
↓
削除当日
④テーブルの総レコード数を取得
↓
⑤削除前に対象のデータを抽出
↓
⑥エクセルに貼り付け
↓
⑦TRUEなら用意していたDELETE文を使い削除
（100件なら100本のDELETE文を用意し、全て正常終了することを確認）
↓
⑧テーブルの総レコード数を確認
（④で取得した件数より100件少ない事を確認）

Comments

[Panzer_vor]

ワークテーブル等の作成は不可能なのでしょうか？
例えば削除対象のレコードを蓄えるワークテーブルの追加がOKであるなら削除作業はもう少し楽が出来るように思います。

Answer 1

Excelが出てくる時点でおかしいです⋯

DELETE文にはRETURNING句があり、削除した行をコレクション変数に受け取れますからそれを印字するなり何なりして「実際に削除されたレコードのリスト」というのを確実に作ることができます。

https://docs.oracle.com/cd/E16338_01/appdev.112/b56260/tuning.htm#BCGHDEJH

対象データを抽出し、そのあとで削除という手順も大丈夫なのでしょうか。抽出作業と削除作業の間に新しく削除すべきデータが出現する可能性があります。抽出というのがWHERE句で記述可能なのなら、それをDELETE文に渡すことでタイムラグに何かが紛れ込む心配がなくなります。

Comments

[Panzer_vor]

質問者じゃないですが、RETURNING句というのがあったんですね…。DBでは一番Oracleにお世話になってるのに全く知らなかったです^^;

Answer 2

正直な所、エビデンスとしてどこまで担保する必要があるのかに依存しますよね…。

それに日々対象が変わるというお話から、更新が多く発生するテーブルの匂いもしますし…。

削除条件に当てはまった時点で問答無用にDELETEかけても良いのかという点にも懸念がありますし…。

エビデンスとしてどこまで要求されるのものなのでしょうか？

例えばエビデンスとして以下を担保しなければならない場合

• 事前に削除対象をリストすること(削除時に思わぬデータが消されたという自体への対策としてはあり得る)
• 削除対象が間違いなく消されたことを確認できること(削除漏れがないことの確認)
• 削除対象として上げたもののみが削除されたことの確からしさを担保すること

想定される一番厳しいレベルで考えると上記3点を満たす必要が出てくるのではと思ってます(そこまで要るかって話もありますが)。

上記レベルのエビデンスを求められると削除結果からのリストではエビデンス不足と言われる恐れあります。(削除結果からのエビデンスではNGとか言うのも変な話かもですが)

後は想定外のものが削除された場合はどう対応するのか？
トランザクション内で削除データをロールバックするのか？
トランザクション内でリカバリできないならフラッシュバックテーブルで戻すのか？
それ以前に想定外に削除され過ぎないよう予防策を張るのかetc…

###削除され過ぎないことが優先なら
想定外の削除を防ぐことが必須であれば、事前(前日までなど)に削除対象をリスト化が必須なので、
この場合はワークテーブルを作って良いならば削除対象レコードを蓄えるものを作っちゃいましょう。

そのワークテーブルを利用して削除を実施すれば、削除対象として上げたもののの削除漏れは高確率で防げるでしょう。
心配でしたら、
0. 本テーブルとワークのINTERSECTにより取得した結果が0件であることを確認
0. 削除前の本テーブルの件数と削除後の本テーブルの件数差異がワークの件数に一致することを確認

ここまでやれば誰も文句は言わないでしょう。
多分ここまで求められることはないでしょうが。

###削除漏れを起こさないことが優先なら
今のやり方自体が削除漏れを起こすやり方となってるので、
WHERE条件で書けるならWHERE句にまとめて一括削除しましょう。

他の回答者さんを参考に、
削除結果をリスト化する仕組みを作れば、リスト作成の手間も省けて万々歳です。

要求が厳しくないならこちらで十分でしょう。

ただし想定外に消されちゃったのでリカバリしたいとなったら一度リカバリしてから、想定外分を除いて削除する手間は発生します。

###最後に
長くなりましたが、
結局はエビデンスとしてどの程度まで担保するかで戦略は変わるので、
それに応じて対応方法を検討すると良いでしょう。

Comments

[Orlofsky]

確かに。
Oracleを19年くらい使っているけど、DELETEしたデータをエビデンスに残す案件って経験ないですね。エビデンスを作る時間や費用をもっと建設的な方向に使った方がよさそうな。

[Panzer_vor]

ISMS外部監査とかが伴う運用業務とかだと、
ユーザ同意を取るため削除データを事前にリスト化して云々は考えられますがね。
証跡とかユーザ同意得てないとか指摘くらいかねないので。

ただその場合でもある程度自動化を図って効率化していかないとやってられなくなりますがね^^;

[Orlofsky]

情報セキュリティ監査の重要性
http://www.atmarkit.co.jp/ait/articles/0302/28/news035.html
正社員じゃなくて社外からの助っ人でやっているので、直接セキュリティ方面の作業をすることはありません。トリガーであるテーブルへの更新情報を履歴テーブルに取ったり、ファイングレイン監査を使ったり、などの要求は珍しくありませんが、DELETE情報だけExcelに、、、って今時、自動小銃が普通に使われている現代に火縄銃で対抗しているような違和感がぬぐえません。

[Panzer_vor]

削除データのエビデンス単体で見ると僕もナンセンスかと思います。

ただデータ整理などの業務で、ユーザに確認してもらうという意味で、
削除対象をリスト化するということはあり得るのかなと。

その場合でもそのリスト以外にユーザ承認もらってやってますとかその辺りの手続きがエビデンスとして別途必要となるのですが。

でも多分今回の質問は、そういった意味は含んでいない気はしてます

Answer 3

ExcelにDELETEするデータを記録するのも手間なので、DELETE時トリガーで別テーブルに履歴を取っては？
CREATE TRIGGER

Answer 4

「削除対象が日々変わっている」ってのがよくわかりませんが、
200万件の削除とはごっつい作業ですなあ。
REDOログがあふれて止まっちゃいそう。(^_^;

さて、削除するからにはなんらかの削除条件がありますよね。
それをプログラミング的に削除SQLを生成するとして、
そのプログラムの正しさを担保するのが分かりやすいかなあ。
できれば、ある程度小分けにして削除できるなら
そうした方がリスクは減らせると思います。
で、どういう資料を残せばいいかというと…
どうしたもんですかね?