Oracle10gからある地味な機能だが。。。

Flashback DropはOracle10gから登場した機能だが、正直必要性を感じることはなかった。
テーブルを間違って削除してしまうようなことは通常考えられないし、自分自身そのようなことで困ったことがなかったからだ。

ところが、過去3年間に2件ものヒューマン・エラーによるテーブルの削除事例を目にした。

いずれの事例でも幸いなことにバックアップが取得されていたので、Point-in-Timeリカバリによって最終的にはリカバリに成功したのだが、うち1件は本番環境での誤操作で、極めて莫大な損害が出たと聞いた。
他の事例も最終テスト段階での誤操作だったので、リカバリまでにテストを止めざるを得ず大きな影響を与えたらしい。

両方の事例で関係者に状況をヒアリングしたのだが、いずれも「Recyclebin」初期化パラメータはデフォルトの「on」から意図的に「off」となっており、Flashback Dropは使えない状態だった。

もし、Flashback Dropが使えていたら

もし、両方の事例で「Recyclebin」パラメータをデフォルトのままとしていたら、誤って削除してしまったテーブルを簡単な操作で、しかもほんの数秒で元に戻すことができたはずである。

一度でもFlashback Dropを体験したことのある人であればその効果が計り知れないことは容易に理解できるのであるが、残念なことにそれらでは共に使えなく、何時間もかけてバックアップから戻すはめになったのだ。

なぜRecyclebinをoffにしていたのか？

結論から言えばデフォルトのままで何ら問題はないのだが、なぜ意図的に「Recyclebin 」が「off」になっていたのかその理由を聞いてみた。

• テーブルを削除しても「ゴミ箱」に残るということは、SYSAUXかどこかにある「ゴミ箱」領域に移動するのではないか？余計なI/Oが発生するのは嫌だ。
• 「ゴミ箱」がいつの間にか一杯になって無駄な領域を占有するのは嫌だ。どうせ「ゴミ」なんだからさっさと消してしまいたい。
• 新機能にはいつも泣かされてきたから、こいつもきっと悪さをするに違いない。9iまではこんな機能なかったのだから要らない。
• 今のバージョンは11gだけど、10gの時からoffにしているからoffのまま。
• そもそも、間違ってDropするなんてことはしないので、余計な機能は要らない。

マニュアルを熟読すれば全くの誤解であることがわかるのだが、一度誤解してしまうとベテランと言われるエンジニアほど頑ななままになる。

だから、デフォルトからわざわざ変更することになる。

検証してみた

「マニュアルにはこう書いてある。」と言っても誤解を解くのは難しいし、自分自身どのような仕組みで動いているのか知りたかったので検証してみることにした。

準備

容量管理をどのようにしているのかを確認したいので、640KBという小さな表領域を作成することにした。

環境は、Oracle 12c EE (12.1.0.2)for Windows(Non-PDB)を使用した。

CREATE TABLESPACE TS_SMALL
DATAFILE 'C:\APP\ORACLEHOME\ORADATA\ORA12C\TS_SMALL01.DBF' SIZE 640K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

データ作成

データはSCOTTスキーマのオブジェクトをインポートすることで簡単に作成した。

確認用スクリプト

stsck.sql

@@uext
@@useg
@@uind
@@ucon
@@usep
@@rbin

uext.sql

TTITLE LEFT '--- エクステント情報 ---'
col EXTENT_NAME for a32
col EXTENT_TYPE for a15
select
 SEGMENT_NAME EXTENT_NAME
,SEGMENT_TYPE EXTENT_TYPE
,TABLESPACE_NAME
,BLOCKS
from
 USER_EXTENTS
order by
 EXTENT_NAME
;
TTITLE OFF
select
 (sum(BLOCKS)+8)/80*100 "TOT_EXT%"
from USER_EXTENTS
/

useg.sql

TTITLE LEFT '--- セグメント情報 ---'
col SEGMENT_NAME for a32
col SEGMENT_TYPE for a15
select
 SEGMENT_NAME
,SEGMENT_TYPE
,TABLESPACE_NAME
,BLOCKS
from
 USER_SEGMENTS
order by
 SEGMENT_NAME
;
TTITLE OFF
select
 (sum(BLOCKS)+8)/80*100 "TOT_SEG%"
from
 USER_SEGMENTS
/

uind.sql

TTITLE LEFT '--- インデックス情報 ---'
col INDEX_NAME for a32
col TABLE_NAME for a32
select
 INDEX_NAME
,TABLE_NAME
from
 USER_INDEXES
order by
 INDEX_NAME
;
TTITLE OFF

ucon.sql

TTITLE LEFT '--- 制約情報 ---'
col CONSTRAINT_NAME for a32
col TABLE_NAME for a32
col INDEX_NAME for a32
select
 CONSTRAINT_NAME
,TABLE_NAME
,CONSTRAINT_TYPE
,INDEX_NAME
,STATUS
from
 USER_CONSTRAINTS
order by
 CONSTRAINT_NAME
;
TTITLE OFF

usep.sql

TTITLE LEFT '--- 表領域使用率 ---'
select * from DBA_TABLESPACE_USAGE_METRICS
where TABLESPACE_NAME = 'TS_SMALL'
/

rbin.sql

TTITLE LEFT '--- リサイクルビン情報 ---'
select
 OBJECT_NAME
,ORIGINAL_NAME
,OPERATION
,TYPE
,TS_NAME
,DROPSCN
,CAN_UNDROP
,CAN_PURGE
from
 USER_RECYCLEBIN
order by
 DROPSCN
/

初期状態

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


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

続く