[Autonomous Database]Flashback Dropを試す


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

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

出典：[Autonomous Database 技術FAQ](https://oracle-japan.github.io/ocidocs/faq/services/autonomous/autonomous-database-faq/)

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

参考：[しばちょう先生の試して納得！DBAへの道　第38回](https://blogs.oracle.com/otnjp/post/shibacho-038)


# 前提
* テスト用ユーザ（今回はSCOTT）を作成済み

# 検証手順
## 1. テストデータの作成
TAB38a表（索引、主キー）を作成
```sql
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表の作成
```sql
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;
```

テスト表の制約を確認
```sql
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 ;
```
表示例
```sql
   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表の削除
```sql
drop table TAB38b;
```

リサイクルビンの確認
```sql
show recyclebin
```
表示例（TAB38Bとその索引が確認できる）
```sql
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 
```

リサイクル・ビン内のオブジェクトの確認
```sql
select * from USER_RECYCLEBIN;
```
```sql
                      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 
```

テスト表の制約を確認
```sql
select OWNER, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, STATUS, VALIDATED
from USER_CONSTRAINTS order by 1,2,3 ;
```
表示例（TAB38Bが消えている）
```sql
   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```表をリサイクル・ビンからリストア
```sql
FLASHBACK TABLE TAB38B TO BEFORE DROP;
```

TAB38b表のレコード数を確認
```sql
select /*+ INDEX_FFS(T1) */ COUNT(*) from TAB38B T1;
```
```sql
   COUNT(*) 
___________ 
      27776 
```
リサイクル・ビン内のオブジェクトの確認
```sql
select * from USER_RECYCLEBIN;
```
```sql
no rows selected
```

## 4. Flashback Drop後の索引の確認
TAB38b表の索引の名前を確認（索引名が変わっている）
```sql
select INDEX_NAME, TABLE_NAME from USER_INDEXES where TABLE_NAME='TAB38B';
```
```sql
                       INDEX_NAME    TABLE_NAME 
_________________________________ _____________ 
BIN$2kwlr5Vt0lXgUwwUAArXeg==$0    TAB38B        
```

索引名をもとに戻す
```sql
ALTER INDEX "BIN$2kwlr5Vt0lXgUwwUAArXeg==$0" RENAME TO PK_TAB38B_COL1 ;
```
```sql
select INDEX_NAME, TABLE_NAME from USER_INDEXES where TABLE_NAME='TAB38B';
```
```sql
       INDEX_NAME    TABLE_NAME 
_________________ _____________ 
PK_TAB38B_COL1    TAB38B        
```

## 5. Flashback Drop後の制約の確認
各種制約の状態を確認
```sql
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 ;
```
制約名が変わっている（参照整合性制約が消えている）
```sql
   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制約の制約名をもとに戻す
```sql
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文で追加し直す
```sql
alter table TAB38b add constraint FK_TAB38a_COL1 FOREIGN KEY (COL3) references TAB38a(COL1) ; 
```

変更できたか確認
```sql
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 ;
```
```sql
   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                          
```