Oracle10g フラッシュバック アゲインの巻 その7

<Oracle10g フラッシュバック アゲイン の巻 その7>
ペンネーム:びー・うぃりー

先週に引き続きフラッシュバックに関する動作確認をしてまいります。
今週は「フラッシュバック・ドロップ」です。

一旦削除してしまった表を復元する機能です。これはWindowsでおなじみの
「ゴミ箱」と似ています。従来のOracleではDropしてしまった表を復元するこ
とはできませんでした。Oracle10gではこの「ゴミ箱」機能を追加する事で表
の復活をサポートしてます。Recyclebin領域ともよばれてます。

ただRecyclebin領域といっても特別な表領域があるわけでもなく表をDropして
もすぐに解放せずに違う名前で保管しているだけです。
早速の確認してみましょう。

■環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options

■はじめ!!!

早速、現在所有しているオブジェクトの名前と種類を確認します。

SQL> SELECT OBJECT_NAME ,OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
PK_FLASHBACK_TEST                        INDEX
FLASHBACK_TEST                           TABLE

FLASHBACK_TESTを削除します。

SQL> DROP TABLE FLASHBACK_TEST;
Table dropped.

SQL> SELECT OBJECT_NAME ,OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
BIN$k7agxkkzRPabXvQ2Tgevxg==$0           TABLE
BIN$gTidcs+aTxqr0/L/bihIMA==$0           INDEX

名前が変更されてます。それでも確認できますから・・。
注:ダブルクォートで囲んでください。

SQL> SELECT * FROM "BIN$k7agxkkzRPabXvQ2Tgevxg==$0";

COL1  COL2
----- -----
11111 AAAAA
22222 BBBBB
33333 CCCCC

ではフラッシュバックテーブルで復元します。

SQL> FLASHBACK TABLE "BIN$k7agxkkzRPabXvQ2Tgevxg==$0" TO BEFORE DROP;
Flashback complete.

元のテーブル名が複数存在する場合も考慮して、この”へんてこな”テーブル
名を指定するのが得策です。

また、FLASHTABLE “BIN$k7agxkkzRPabXvQ2Tgevxg==$0” TO BEFORE DROP RENAME
TO 別名テーブル;とすれば、別名として復元できます。

ただし、INDEX名は”へんてこ”名のままですが、昔の名前でも使用可能なよ
うです。

SQL> SELECT OBJECT_NAME ,OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
FLASHBACK_TEST                           TABLE
BIN$gTidcs+aTxqr0/L/bihIMA==$0           INDEX

SQL> SELECT /*+ USE_INDEX(A,PK_FLASHBACK_TEST) */ COL1 FROM FLASHBACK_TEST A 
     WHERE COL1='11111';

COL1
-----
11111

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=0 Card=1 Bytes=7)
   1    0   INDEX (UNIQUE SCAN) OF 'BIN$gTidcs+aTxqr0/L/bihIMA==$0' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=7)

・・・長すぎ~~です。少しまぎらわしいですね・・・。

次は、複数の表がゴミ箱に入っている場合に見分け方です。
ニューフェイスの「DBA_RECYCLEBIN」と「USER_RECYCLE」にゴミ箱内の表名な
どの情報が格納されてます。SELECT検索も可能ですが次のように簡単に検索で
きます。

SQL> DROP TABLE FLASHBACK_TEST;
Table dropped.

SQL> CREATE TABLE FLASHBACK_TEST (COL1 CHAR(5),COL2 CHAR(5));
Table created.

SQL>  DROP TABLE FLASHBACK_TEST;
Table dropped.

SQL>  SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_TEST   BIN$ifsnzekIQSuxRs26sv2hrg==$0 TABLE        2005-03-26:23:31:16
FLASHBACK_TEST   BIN$Ql2UpYPnTYCdquJSquGreg==$0 TABLE        2005-03-26:23:29:51

続いて、ごみ箱を空にする方法です。PURGEコマンドを利用します。
ゴミ箱全体を掃除する場合 「PURGE RECYCLEBIN」
特定の表や索引を掃除する場合 「PURGE TABLE (またはINDEX) ゴミ箱内の名称」
データベース全体のゴミ箱を掃除する場合 「PURGE DBA_RECYCLEBIN」※DBA権限が必要

では一気にお掃除します。

SQL> CONN / AS SYSDBA
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.

SQL> CONN ORAORA/ORAORA
Connected.
SQL> SHOW RECYCLEBIN
SQL> <何も返ってきません>

きれいさっぱりです。Oracleまかせでお掃除も可能ですが、やはり自分掃除し
たほうが気持ちいいです。。

ゴミ箱掃除が面倒だぁーという方へ。では、いきなり削除しましょう。
DROP文にキーワード「PURGE」をつけます。

SQL> DROP TABLE FLASHBACK_TEST PURGE;
Table dropped.

SQL> SELECT OBJECT_NAME ,OBJECT_TYPE FROM USER_OBJECTS;
no rows selected

私はゴミ箱など必要ない!という方へ。わかりましたゴミ箱をなくします。

SQL> CONN / AS SYSDBA 
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
  2  FROM x$ksppi a, x$ksppcv b
  3  WHERE a.indx = b.indx
  4  AND a.ksppinm like '%recycle%'
  5* ORDER BY a.ksppinm

KSPPINM                                  KSPPSTVL                     KSPPSTDF
---------------------------------------- ---------------------------- --------
_db_percent_hot_recycle                  0                            TRUE
_recyclebin                              TRUE                         TRUE
buffer_pool_recycle                                                   TRUE
db_recycle_cache_size                    0                            TRUE

SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH; 
System altered.

SQL> SELECT OBJECT_NAME ,OBJECT_TYPE FROM USER_OBJECTS;
no rows selected

RECYCLEBIN機能停止です。

※この隠しパラメータの変更は正式サポートなしです。ご注意を・・・

今週はここまで。

皆さん、春がやってきました!!!スカッと仕事しましょう!!