Oracle APEXのSQLコマンドでは、実行をクリックするとコミットまでされます。
そのため、例えばwhere句をつけるつもりだったのに、うっかり以下のようなdelete文を実行してしまった、ということもあったりします。delete文を実行する表の名前を間違った、という場合もあり得ます。
delete from emp;
ロールバック文は適用されません。すべての文は自動的にコミットされます。
これが本番で使われているデータベースだったりすると、相当焦ります。
オラクルのデータベースでは、読取り一貫性を提供するために、変更前のデータがUNDO表領域に保存されています。このUNDO表領域に保存されている変更前のデータは、Flashback Queryを使って取り出すことができます。
通常のSELECT文にAS OF 時刻を付加します。例えば20分前の表EMPの情報を取り出し、表EMP_RECOVERに保存するには、以下のSQLを実行します。
create table emp_recover as
select * from emp as of timestamp (systimestamp - interval '20' minute);
指定する時刻はサーバー側の時刻です。データベースが日本時間ではなくUTCで動いている場合もあるため、時刻を指定する際にはあらかじめサーバー側のタイムゾーンを確認しておきます。確認方法は色々ありますが、SYSTIMESTAMPを表示させるのもひとつの方法です。
select systimestamp from dual;
to_timestampを使って時刻を指定し、変更前のデータを取り出します。
create table emp_recover2 as
select * from emp as of timestamp to_timestamp('2021/10/07 06:20:00','YYYY/MM/DD HH24:MI:SS');
今回の例のように全件消してしまった場合は、insert select文で回復できます。
insert into emp select * from emp_recover;
表にトリガーが設定されている場合、データの回復時に意図しない更新が発生することがあります。特に監査列(行の作成者、更新者、作成時刻、更新時刻を保存する列)の更新をトリガーで行っている場合は、データを回復する前にトリガーを無効化しておく必要があります。
オブジェクト・ブラウザを開き、データをリカバリする表を選択します。トリガーのタブを選択し、設定されているトリガーを確認し、無効化を行います。
トリガーであれば何でも無効化しないといけない、ということではありません。例えば主キーを設定するトリガーは、主キーとなる値の指定が無いときに限り自動的に主キーを生成する、というコードが一般的であるため、無効化する必要はありません。このようなトリガーを無効化するとデータの整合性に影響が出る可能性があるため、データの回復を阻害するトリガーのみを選んで無効化します。
例えば、サンプル・データセットの表EMPに設定されているトリガーEMP_TRG1のコードは以下なので、無効化は不要です。
create or replace trigger emp_trg1
before insert on emp
for each row
begin
if :new.empno is null then
select emp_seq.nextval into :new.empno from sys.dual;
end if;
end;
間違ってUPDATEをしてしまった場合の回復には、以下のようなUPDATE文が使えます。表EMPの全ての行の列SALを変更前の値に戻します。
update emp
set emp.sal =
(
select emp_recover.sal
from emp_recover
where emp_recover.empno = emp.empno
);
データを回復した後に、無効化したトリガーがあれば有効化しておきます。
どの程度まで遡って変更前のデータを回復できるかは、初期化パラメータのundo_retentionの設定とUNDO表領域の容量に依存します。
Autonomous Transaction Processingのインスタンスで確認したところ、900(秒)つまり１５分でした。UNDO表領域に余裕があればそれよりも以前の変更も保存されますが、データの回復はundo_retentionで指定された時間内で行うのがよいでしょう。
ちなみにAPEXのアプリケーションをエクスポートする際に指定できる、現在から〇〇分前も、フラッシュバック・テクノロジーを使っています。
begin
dbms_flashback.enable_at_time(
query_time => (systimestamp - interval '10' minute)
);
for r in (select * from emp)
loop
dbms_output.put_line(r.ename);
end loop;
dbms_flashback.disable;
end;
間違って表をドロップした場合には、FLASHBACK TABLEを使うことができます。初期化パラメータのrecyclebinがonであれば有効です。Autonomous Databaseでは、作成時に有効になっています。
例えばデータの回復が終了したので、表EMP_RECOVERをドロップしたとします。
drop table emp_recover;
まだ回復が必要なデータがあったとします。表EMP_RECOVERが必要です。まず、最初にビューUSER_RECYCLEBINを確認します。
select * from user_recyclebin;
リサイクルビン（つまりゴミ箱）に残っていれば、回復することができます。
表EMP_RECOVERを回復するために、以下のFLASHBACK TABLE文を実行します。
flashback table emp_recover to before drop;
SQLの実行が成功していれば、表EMP_RECOVERが回復されています。検索して確認してみます。
select * from emp_recover;
データが表示され、表EMP_RECOVERが回復されたことが確認できます。
表をドロップしてもリサイクルビンに残っていると、表領域の空き容量が増えません。物理的に削除し領域を再利用できるようにするには、リサイクルビンのパージを実行します。
再度、表EMP_RECOVERをドロップし、リサイクルビンの内容を確認します。
リサイクルビンのパージするために、以下のSQLを実行します。
purge recyclebin;
リサイクルビンを検索すると一行も表示されず、すべて削除されたことが確認できます。パージ後はFLASHBACK TABLE文を実行しても表を回復することはできません。
select * from user_recyclebin;
purge句を付けて表をドロップするとリサイクルビンに入らず、物理的に削除されます。表領域の空きを確保するために表をドロップする場合は、purge句を付けるのも良いでしょう。
drop table emp_recover purge;
Oracle APEXの開発で使用するワークスペースは複数の開発者が参加しています。リサイクルビンに残っている表はAPEXのアプリケーション開発者の誰がドロップしたのか分からない場合があり、また、その人が表を回復したいと思うことがあるかも分かりません。なので、リサイクルビンのパージ処理はワークスペースで開発作業を行っている人に確認してから実施した方が良いでしょう。
とにかく、オペミスなどをしてしまっても落ち着いて対応するのが一番大切です。
完
