日別アーカイブ: 2016/02/28

Flashback Dropの検証②

前回のおさらい

JPOUG20160223.001

Flashback Dropの検証を行う環境の初期状態である。

オブジェクトはテーブルとインデックス、あわせて5つ。すべて1エクステント=8ブロック=64KBずつを占めている。これの他に表領域の管理情報と思われる1エスクテント分を合せて480KBの領域となるので、全体(640KB)に対する使用率は60%、空き領域は40%となっている。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

EMP表をDropする。

それでは、まず最初にEMP表をDropして状況を確認してみる。

SQL> drop table EMP;

表が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        40


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
BIN$YIfQE0ilRXy+VvPcGtg1WA==$0   BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   P  BIN$IM3P5MOwSlazE4G+zy6X3w==$0   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                32              80           40


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   PK_EMP          DROP       INDEX      TS_SMALL      3323577 NO     YES
BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   EMP             DROP       TABLE      TS_SMALL      3323581 YES    YES

テーブルとインデックスが「BIN$」で始まる名前にRenameされているだけでなく、PK制約もRenameされていることがわかる。

しかし、リサイクルビン情報にはFK制約の情報はない。つまり削除されている。

また、USER_RECYCLEBIN.CAN_UNDROP列を見ると、テーブルは「YES」となっているのに、インデックスの方は「NO」となっている。この違いについては次の操作で明らかになるので追って解説する。

さらに、セグメント情報を見ると、Dropされたオブジェクトは依然として「TS_SMALL」表領域内に存在していることがわかる。つまり、Drop(削除)されてゴミ箱(リサイクルビン)に入るというのは概念的なことであって、物理的には同じ表領域に存在しているのである。

SYSAUX表領域などに移動されるわけではない。

EMP表をDropした状態を図に示すと以下のようになる。

JPOUG20160223.012

JPOUG20160223.013
参照整合性制約(FK_EMP)については、リサイクルビンで管理されることなく即時に削除されていることがわかる。

Flashback Drop実行

それでは「FLASHBACK TABLE」コマンドにより、EMP表を削除前に戻してみよう。

SQL> flashback table EMP to before drop;

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

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   EMP
PK_DEPT                          DEPT


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
BIN$YIfQE0ilRXy+VvPcGtg1WA==$0   EMP                              P  BIN$IM3P5MOwSlazE4G+zy6X3w==$0   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

SQL> Insert into FD.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('82-01-23','RR-MM-DD'),1300,null,10);
Insert into FD.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('82-01-23','RR-MM-DD'),1300,null,10)
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(FD.BIN$YIfQE0ilRXy+VvPcGtg1WA==$0)に反しています

エクステントが復活したために、空き領域は初期状態の40%に戻っている。

テーブルは削除前と同じ名前に戻ったが、インデックスはRenameされたままである。さらにPK制約もRenameされたままであることがわかる。つまり、USER_RECYCLEBIN.CAN_UNDROP列が「YES」のものは削除前のオブジェクト名に戻るが、「NO」のものは戻らないことを示している。

さらに、参照整合性制約は削除されたままで元に戻ることはないがPK制約は有効であるので、制約違反となるレコードをInsertしようとするとORA-0001エラーが発生する。

JPOUG20160223.014

JPOUG20160223.015

インデックスやPK制約はテーブルが存在すれば、削除/再作成で元の名前に戻すことは可能であるため、Flashbck Dropの中ではRenameに関する動作が行われないのかもしれない。

今回はここまで。