Oracle dropしたテーブルを元に戻したい

dropしたテーブルはゴミ箱へ移動するだけで、直ぐに消える事はありません。ゴミ箱の中はRECYCLEBINで確認する事ができます。（DBA_RECYCLEBINでは全スキーマのゴミ箱を確認できます）見方を変えると、消したつもりが「領域的にはまだ開放していない」って事になります。そのため、本当に消したい時はdorop table ex01 PURGE; とする必要があります。

検証用データ作成

CREATE TABLE DABADA.EX003 ( 
    lno	     NUMBER
   ,name     VARCHAR2(10)
   ,birthday DATE
   ,note     CLOB
) 
TABLESPACE TBS02
;
alter table ex003 add constraint pk_ex003 primary key (lno)
;
create index idx01_ex003 on ex003(name)
;
comment on table DABADA.EX003 is 'FBテスト用';
comment on column ex003.lno is '管理No.';
comment on column ex003.name is '名前';
comment on column ex003.birthday is '誕生日';
comment on column ex003.note is '備考';

insert all 
into EX003 (LNO, NAME, BIRTHDAY, NOTE) values(1,'西川','2001/01/03','コーラが好きです')
into EX003 (LNO, NAME, BIRTHDAY, NOTE) values(2,'北川', '2000/12/13','納豆は嫌いです')
select 1 from dual;

commit;

--
-- 後の検証確認のために事前に今回作成したテーブルのインデックス一覧
-- を表示しておきます。注１はCLOBカラムを使うとシステムにて自動で作成される
-- インデックスです。
--
COLUMN OWNER        FORMAT A10
COLUMN INDEX_NAME   FORMAT A30
COLUMN INDEX_TYPE   FORMAT A10
COLUMN TABLE_OWNER  FORMAT A10
COLUMN TABLE_NAME   FORMAT A10
COLUMN GENERATED    FORMAT A6
COLUMN GENERATED    HEADING 'GENE|RATED'

select i.OWNER,i.INDEX_NAME,i.INDEX_TYPE,i.TABLE_OWNER,i.TABLE_NAME,i.GENERATED
 from dba_indexes i where i.TABLE_NAME='EX003' and i.OWNER='DABADA'
order by i.OWNER,i.INDEX_NAME
;

                                                                           GENE
OWNER      INDEX_NAME                     INDEX_TYPE TABLE_OWNE TABLE_NAME RATED
---------- ------------------------------ ---------- ---------- ---------- ------
DABADA     IDX01_EX003                    NORMAL     DABADA     EX003      N
DABADA     PK_EX003                       NORMAL     DABADA     EX003      N
DABADA     SYS_IL0000114018C00004$$       LOB        DABADA     EX003      Y　　←注１

SQL>

drop を行いゴミ箱を確認する

drop table DABADA.EX003
;

DBA_RECYCLEBINでゴミ箱の中を確認する

select r.OWNER,r.OBJECT_NAME,r.ORIGINAL_NAME,r.OPERATION,r.TYPE,r.DROPTIME
from DBA_RECYCLEBIN r where r.OWNER='DABADA'
order by r.TYPE,r.ORIGINAL_NAME,r.DROPTIME
;

ゴミ箱の確認結果

-- 注1 CLOBカラムを定義すると作成されるLOBオブジェクト
-- 注2 CLOBカラムを定義すると自動で作成されるLOB INDEX
                                                                       OPERA
OWNER      OBJECT_NAME                    ORIGINAL_NAME                TION  TYPE       DROPTIME
---------- ------------------------------ ---------------------------- ----- ---------- --------------------
DABADA     BIN$5QhP10nxTk3gUw0DqMDWiA==$0 IDX01_EX003                  DROP  INDEX      2022-07-31:00:43:18
DABADA     BIN$5QhP10nyTk3gUw0DqMDWiA==$0 PK_EX003                     DROP  INDEX      2022-07-31:00:43:18
DABADA     SYS_LOB0000114018C00004$$      SYS_LOB0000114018C00004$$    DROP  LOB        2022-07-31:00:43:18 ←注1
DABADA     SYS_IL0000114018C00004$$       SYS_IL0000114018C00004$$     DROP  LOB INDEX  2022-07-31:00:43:18 ←注2
DABADA     BIN$5QhP10nzTk3gUw0DqMDWiA==$0 EX003                        DROP  TABLE      2022-07-31:00:43:18

SQL>

SQL*Plus表示用

set lin 200
set pages 50
COLUMN OWNER  FORMAT A14
COLUMN OBJECT_NAME  FORMAT A30
COLUMN ORIGINAL_NAME  FORMAT A28
COLUMN OPERATION  FORMAT A5
COLUMN OPERATION HEADING 'OPERA|TION'
COLUMN TYPE  FORMAT A10
COLUMN DROPTIME  FORMAT A20

FLASHBACK TABLE ゴミ箱に削除したものを元に戻す

TO BEFORE DROP というオプションを使うと対象のオブジェクトに依存する全てを元に戻す事ができます。

FLASHBACK TABLE DABADA.EX003 TO BEFORE DROP
;

インデックス名が元に戻らないので手動でRENAMEする必要があります

なんだかイマイチのようですが、、もしかしたら何かきちんと元に戻る方法あるのかもですが、ちょと見つかりませんでした。ヒント句でインデックス名を指定しているような場合困ってしまいますね。

SQL> FLASHBACK TABLE DABADA.EX003 TO BEFORE DROP
  2  ;

フラッシュバックが完了しました。

--
-- 当然ゴミ箱は空になります。
--
SQL> select r.OWNER,r.OBJECT_NAME,r.ORIGINAL_NAME,r.OPERATION,r.TYPE,r.DROPTIME
  2  from DBA_RECYCLEBIN r where r.OWNER='DABADA'
  3  order by r.TYPE,r.ORIGINAL_NAME,r.DROPTIME
  4  ;

レコードが選択されませんでした。
--
-- dropしたテーブルも復活しています。
-- 
SQL> select * from ex003;

       LNO NAME                 BIRTHDAY            NOTE
---------- -------------------- ------------------- ------------------------------
         1 西川                 2001-01-03 00:00:00 コーラが好きです
         2 北川                 2000-12-13 00:00:00 納豆は嫌いです

SQL>
--
-- しかしながらインデックス名は元に戻りません。
--
select i.OWNER,i.INDEX_NAME,i.INDEX_TYPE,i.TABLE_OWNER,i.TABLE_NAME,i.GENERATED
 from dba_indexes i where i.TABLE_NAME='EX003' and i.OWNER='DABADA'
order by i.OWNER,i.INDEX_NAME
;

                                                                           GENE
OWNER      INDEX_NAME                     INDEX_TYPE TABLE_OWNE TABLE_NAME RATED
---------- ------------------------------ ---------- ---------- ---------- ------
DABADA     BIN$5QhP10nxTk3gUw0DqMDWiA==$0 NORMAL     DABADA     EX003      N
DABADA     BIN$5QhP10nyTk3gUw0DqMDWiA==$0 NORMAL     DABADA     EX003      N
DABADA     SYS_IL0000114018C00004$$       LOB        DABADA     EX003      Y

SQL>
-- 
-- 以下の通り手動で戻さないといけないようです。
-- 
SQL> alter index "BIN$5QhP10nxTk3gUw0DqMDWiA==$0"  rename to "IDX01_EX003"
  2  ;

索引が変更されました。

SQL> alter index "BIN$5QhP10nyTk3gUw0DqMDWiA==$0"  rename to "PK_EX003"
  2  ;

索引が変更されました。

SQL> select i.OWNER,i.INDEX_NAME,i.INDEX_TYPE,i.TABLE_OWNER,i.TABLE_NAME,i.GENERATED
  2   from dba_indexes i where i.TABLE_NAME='EX003' and i.OWNER='DABADA'
  3  order by i.OWNER,i.INDEX_NAME
  4  ;

                                                                           GENE
OWNER      INDEX_NAME                     INDEX_TYPE TABLE_OWNE TABLE_NAME RATED
---------- ------------------------------ ---------- ---------- ---------- ------
DABADA     IDX01_EX003                    NORMAL     DABADA     EX003      N
DABADA     PK_EX003                       NORMAL     DABADA     EX003      N
DABADA     SYS_IL0000114018C00004$$       LOB        DABADA     EX003      Y

SQL>

指定のテーブルをゴミ箱からも削除する

drop tableした後、ゴミ箱からも削除するには以下の通りです。注意点として、同一名テーブルを２回削除した場合ゴミ箱には異なる名前で２世代分残っていて、最初のpurgeで古いテーブルから削除され、全て削除するには再度purgeを実行する必要あります。またこのpurgeはスキーマ自身のゴミ箱のみ削除可能です。以下例のようにDABADA.EX003をpuregeするにはDABADAスキーマでないと実行できません。