はじめに

Autonomous Database（ADB）でもOracle Databaseの機能であるFlashback機能が利用できるため、試してみました。

Autonomous Database でFlashback機能は利用できますか？
Flashback databaseはリストアの際に内部的に利用されていますが、ユーザーが明示的に利用することはできません。その他のFlashback query, table, drop機能は通常のOracle Databaseと同様に利用可能です。

出典：Autonomous Database 技術FAQ

今回は、Flashback Dropを使用し、誤って削除した表をもとに戻します
手順は下記を参照していますので、詳しくはこちらをご覧ください。

参考：しばちょう先生の試して納得！DBAへの道　第38回

前提

テスト用ユーザ（今回は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