はじめに
Autonomous Database（ADB）でもOracle Databaseの機能であるFlashback機能が利用できるため、試してみました。
Autonomous Database でFlashback機能は利用できますか？
Flashback databaseはリストアの際に内部的に利用されていますが、ユーザーが明示的に利用することはできません。 その他のFlashback query, table, drop機能は通常のOracle Databaseと同様に利用可能です。
今回は、Flashback Dropを使用し、誤って削除した表をもとに戻します
手順は下記を参照していますので、詳しくはこちらをご覧ください。
前提
- テスト用ユーザ（今回はSCOTT）を作成済み
検証手順
1. テストデータの作成
TAB38a表（索引、主キー）を作成
create table TAB38a (COL1 number NOT NULL, COL2 char(1000)) ;
insert into TAB38a select LEVEL, 'A'||to_char(LEVEL) from DUAL connect by LEVEL <=7*128*7 ;
commit;
create unique index PK_TAB38a_COL1 on TAB38a(COL1) ;
alter table TAB38a add primary key (COL1) using index PK_TAB38a_COL1 ;
TAB38b表の作成
create table TAB38b (COL1 number NOT NULL constraint PK_TAB38b_COL1 primary key,
COL2 char(1000),
COL3 number constraint FK_TAB38a_COL1 references TAB38a(COL1),
COL4 char(20)) ;
insert into TAB38b select LEVEL, 'B'||to_char(LEVEL), mod(LEVEL, 7*128*7)+1,'' from DUAL connect by LEVEL <=7*128*31 ;
commit;
テスト表の制約を確認
col OWNER for a8
col TABLE_NAME for a8
col CONSTRAINT_NAME for a16
select OWNER, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, STATUS, VALIDATED
from USER_CONSTRAINTS order by 1,2,3 ;
表示例
OWNER TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME STATUS VALIDATED
________ _____________ __________________ __________________ __________ ____________
SCOTT TAB38A C SYS_C0028501 ENABLED VALIDATED
SCOTT TAB38A P SYS_C0028502 ENABLED VALIDATED
SCOTT TAB38B C SYS_C0028503 ENABLED VALIDATED
SCOTT TAB38B P PK_TAB38B_COL1 ENABLED VALIDATED
SCOTT TAB38B R FK_TAB38A_COL1 ENABLED VALIDATED
親表であるTAB38aと、そのCOL1列に対して参照整合性制約を持つ子表TAB38bを作成できた。
2. リサイクルビンへの移動を確認
TAB38b表の削除
drop table TAB38b;
リサイクルビンの確認
show recyclebin
表示例（TAB38Bとその索引が確認できる）
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-------------- ------------------------------ ----------- -------------------
PK_TAB38B_COL1 BIN$2kwlr5Vt0lXgUwwUAArXeg==$0 INDEX 2022-03-16:00:39:33
TAB38B BIN$2kwlr5Vu0lXgUwwUAArXeg==$0 TABLE 2022-03-16:00:39:33
リサイクル・ビン内のオブジェクトの確認
select * from USER_RECYCLEBIN;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
_________________________________ _________________ ____________ ________ __________ ______________________ ______________________ _________________ _________________ _____________ ____________ __________ ______________ _______________ ________
BIN$2kwlr5Vt0lXgUwwUAArXeg==$0 PK_TAB38B_COL1 DROP INDEX DATA 2022-03-16:00:35:26 2022-03-16:00:39:33 38061407288399 NO YES 99912 99912 99913 72
BIN$2kwlr5Vu0lXgUwwUAArXeg==$0 TAB38B DROP TABLE DATA 2022-03-16:00:35:26 2022-03-16:00:39:33 38061407288420 YES YES 99912 99912 99912 40
テスト表の制約を確認
select OWNER, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, STATUS, VALIDATED
from USER_CONSTRAINTS order by 1,2,3 ;
表示例（TAB38Bが消えている）
OWNER TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME STATUS VALIDATED
________ _________________________________ __________________ _________________________________ __________ ____________
SCOTT BIN$2kwlr5Vu0lXgUwwUAArXeg==$0 C BIN$2kwlr5Vr0lXgUwwUAArXeg==$0 ENABLED VALIDATED
SCOTT BIN$2kwlr5Vu0lXgUwwUAArXeg==$0 P BIN$2kwlr5Vs0lXgUwwUAArXeg==$0 ENABLED VALIDATED
SCOTT TAB38A C SYS_C0028501 ENABLED VALIDATED
SCOTT TAB38A P SYS_C0028502 ENABLED VALIDATED
3. Flashback Dropを使用しリサイクルビンからリストア
TAB38b表をリサイクル・ビンからリストア
FLASHBACK TABLE TAB38B TO BEFORE DROP;
TAB38b表のレコード数を確認
select /*+ INDEX_FFS(T1) */ COUNT(*) from TAB38B T1;
COUNT(*)
___________
27776
リサイクル・ビン内のオブジェクトの確認
select * from USER_RECYCLEBIN;
no rows selected
4. Flashback Drop後の索引の確認
TAB38b表の索引の名前を確認（索引名が変わっている）
select INDEX_NAME, TABLE_NAME from USER_INDEXES where TABLE_NAME='TAB38B';
INDEX_NAME TABLE_NAME
_________________________________ _____________
BIN$2kwlr5Vt0lXgUwwUAArXeg==$0 TAB38B
索引名をもとに戻す
ALTER INDEX "BIN$2kwlr5Vt0lXgUwwUAArXeg==$0" RENAME TO PK_TAB38B_COL1 ;
select INDEX_NAME, TABLE_NAME from USER_INDEXES where TABLE_NAME='TAB38B';
INDEX_NAME TABLE_NAME
_________________ _____________
PK_TAB38B_COL1 TAB38B
5. Flashback Drop後の制約の確認
各種制約の状態を確認
col OWNER for a8
col TABLE_NAME for a6
col CONSTRAINT_NAME for a30
col SEARCH_CONDITION for a20
select TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
STATUS, VALIDATED, SEARCH_CONDITION
from USER_CONSTRAINTS
where TABLE_NAME='TAB38B' and OWNER='SCOTT'
order by 1,2,3 ;
制約名が変わっている（参照整合性制約が消えている）
TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME STATUS VALIDATED SEARCH_CONDITION
_____________ __________________ _________________________________ __________ ____________ _____________________
TAB38B C BIN$2kwlr5Vr0lXgUwwUAArXeg==$0 ENABLED VALIDATED "COL1" IS NOT NULL
TAB38B P BIN$2kwlr5Vs0lXgUwwUAArXeg==$0 ENABLED VALIDATED
主キーとNot Null制約の制約名をもとに戻す
alter table TAB38b rename constraints "BIN$2kwlr5Vs0lXgUwwUAArXeg==$0" to "PK_TAB38B_COL1" ;
alter table TAB38b rename constraints "BIN$2kwlr5Vr0lXgUwwUAArXeg==$0" to "SYS_C0028503" ;
参照整合性制約は消えているので ALTER TABLE文で追加し直す
alter table TAB38b add constraint FK_TAB38a_COL1 FOREIGN KEY (COL3) references TAB38a(COL1) ;
変更できたか確認
select TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
STATUS, VALIDATED, SEARCH_CONDITION
from USER_CONSTRAINTS
where TABLE_NAME='TAB38B' and OWNER='SCOTT'
order by 1,2,3 ;
TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME STATUS VALIDATED SEARCH_CONDITION
_____________ __________________ __________________ __________ ____________ _____________________
TAB38B C SYS_C0028503 ENABLED VALIDATED "COL1" IS NOT NULL
TAB38B P PK_TAB38B_COL1 ENABLED VALIDATED
TAB38B R FK_TAB38A_COL1 ENABLED VALIDATED