しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。”しばちょう”こと柴田長（しばた つかさ）です。
さてさて今回は、Oracle Databaseが提供するデータリカバリ機能の一角を担うフラッシュバック・テクノロジーの内、最も身近（知らないうちに使用している）であろうFlashback Drop機能を体験して頂きたいと思います。
パソコン上で誤って削除してしまった文書や画像ファイルを復旧させられるのがゴミ箱のメリットですが、Oracle Databaseでも同じようにゴミ箱（リサイクル・ビン）が存在します。例えば、バッチ処理が想定外の挙動をしてしまい、誤って表が削除されてしまった場合にFlashback Table文で表を復旧させることが可能ですので、是非、この機会に習得し、万が一に備えておきましょう。今回の演習では、基本的なオペレーションを行いながら少しだけ内部的な動作を確認し、ちょっとした注意点を紹介したいと考えています。
以下の演習をOracle Database 11g Release 2以降のデータベースで試してみてください。マニュアル「Oracle Database 管理者ガイド」の「フラッシュバック・ドロップの使用とリサイクル・ビンの管理」も合わせてご確認下さいね。
【今回ご紹介するネタ一覧（逆引き）】
- リサイクル・ビン内のオブジェクトを確認（演習2）
- Flashback Drop機能で、削除した表をリサイクル・ビンからリストア（演習3）
- リストア後の依存オブジェクトや制約の状態（演習4）
- 依存オブジェクトや制約を削除前の状態へ戻す（演習5）
- Drop Table文でのPurge句を付ける（演習6）
- 依存オブジェクトは個別にパージ処理出来ない場合がある（演習7）
- リサイクル・ビンのパージ処理（演習8）
- パージ処理をしない状態で表領域が枯渇した場合の性能影響（演習9）
1. 次のSQLで今回の演習で使用するTAB38a表とTAB38b表と関連オブジェクトをTRYスキーマ内に作成して下さい。
※ 次の画面キャプチャは2015年3月22日時点で取得したものであり、今後変更される可能性がありますのでご注意ください。
$ sqlplus / as sysdba
SQL>
-- TBS38表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成
create tablespace TBS38 datafile '+DGN(DATAFILE)' size 1500m ;
create user TRY identified by TRY default tablespace TBS38 ;
grant connect, resource, dba to TRY ;
-- TAB38a表の作成
connect TRY/TRY
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 ;
--> 6272 rows created.
commit ;
create unique index PK_TAB38a_COL1 on TAB38a(COL1) ;
alter table TAB38a add primary key (COL1) using index PK_TAB38a_COL1 ;
-- TAB38b表の作成
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 ;
--> 27776 rows created.
commit ;
set linesize 120 pages 5000
col SEGMENT_NAME for a32
col TABLESPACE_NAME for a8
select TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME, sum(BYTES/1024/1024)
from DBA_SEGMENTS
where TABLESPACE_NAME='TBS38'
group by TABLESPACE_NAME, rollup(SEGMENT_TYPE, SEGMENT_NAME)
order by 1,2 ;
TABLESPA SEGMENT_TYPE SEGMENT_NAME SUM(BYTES/1024/1024)
-------- ------------------ -------------------------------- --------------------
TBS38 INDEX PK_TAB38A_COL1 .1875
TBS38 INDEX PK_TAB38B_COL1 .5
TBS38 INDEX .6875
TBS38 TABLE TAB38A 8
TBS38 TABLE TAB38B 32
TBS38 TABLE 40
TBS38 40.6875
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 ;
OWNER TABLE_NA C CONSTRAINT_NAME STATUS VALIDATED
-------- -------- - ---------------- -------- -------------
TRY TAB38A C SYS_C004096 ENABLED VALIDATED
TRY TAB38A P SYS_C004097 ENABLED VALIDATED
TRY TAB38B C SYS_C004098 ENABLED VALIDATED
TRY TAB38B P PK_TAB38B_COL1 ENABLED VALIDATED
TRY TAB38B R FK_TAB38A_COL1 ENABLED VALIDATED
まずは毎度お馴染みの指の準備体操ですね。今回は親表であるTAB38aと、そのCOL1列に対して参照整合性制約を持つ子表TAB38bの2つの表を作成して頂きました。それぞれには主キーも作成していて、TBS38表領域上で約40MBを消費していることが確認出来ています。もう一つ、ここで把握しておくべきことは制約です。USER_CONSTRAINTSビューでは合計で5つの制約を確認することが出来ますが、どれが何の制約だか分かりますか？
そうですね。CONSTRAINT_TYPE列の値を見れば何となく分かるでしょう。マニュアル「Oracle Databaseリファレンス」の「ALL_CONSTRAINTS」を参照して頂ければ、「C」が表のチェック制約、「P」が主キー制約、「R」が参照整合性制約であることが理解できると思います。
準備万端と言う事で、早速ですが、TAB38b表を削除してリサイクル・ビン（ゴミ箱）へ移動したか否かを確認してみましょう。
2. TAB38b表を削除することで、リサイクル・ビンへ移動したことを確認して下さい。また、TBS38表領域の使用量を確認して下さい。
$ sqlplus TRY/TRY SQL> -- TAB38b表の削除 drop table TAB38b ; -- リサイクル・ビンへ移動したことの確認 show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ------------- ------------------------------ ------------ ------------------- TAB38B BIN$FJYxlNyTDHzgU2Y4qMCU/Q==$0 TABLE 2015-04-26:08:51:38 -- TBS38表領域の使用量を確認 set linesize 120 pages 5000 col SEGMENT_NAME for a32 col TABLESPACE_NAME for a8 select TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME, sum(BYTES/1024/1024) from DBA_SEGMENTS where TABLESPACE_NAME='TBS38' group by TABLESPACE_NAME, rollup(SEGMENT_TYPE, SEGMENT_NAME) order by 1,2 ; TABLESPA SEGMENT_TYPE SEGMENT_NAME SUM(BYTES/1024/1024) -------- ------------- -------------------------------- -------------------- TBS38 INDEX BIN$FJYxlNySDHzgU2Y4qMCU/Q==$0 .5 TBS38 INDEX PK_TAB38A_COL1 .1875 TBS38 INDEX .6875 TBS38 TABLE BIN$FJYxlNyTDHzgU2Y4qMCU/Q==$0 32 TBS38 TABLE TAB38A 8 TBS38 TABLE 40 TBS38 40.6875 -- 各種制約の状態確認 col OWNER for a8 col TABLE_NAME for a30 col CONSTRAINT_NAME for a30 select TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, STATUS, VALIDATED from USER_CONSTRAINTS where OWNER='TRY' order by 1,2,3 ; TABLE_NAME C CONSTRAINT_NAME STATUS VALIDATED ------------------------------ - ------------------------------ ------- --------- BIN$FJYxlNyTDHzgU2Y4qMCU/Q==$0 C BIN$FJYxlNyQDHzgU2Y4qMCU/Q==$0 ENABLED VALIDATED BIN$FJYxlNyTDHzgU2Y4qMCU/Q==$0 P BIN$FJYxlNyRDHzgU2Y4qMCU/Q==$0 ENABLED VALIDATED TAB38A C SYS_C004096 ENABLED VALIDATED TAB38A P SYS_C004097 ENABLED VALIDATED
如何でしょうか？リサイクル・ビンの中を確認する最も簡単なコマンドは、「show recyclebin」です。知ってました？意外と知らない人が多いのではないかと思っている私も、最近まで実は知りませんでした。。。でもって、ORIGINAL NAME （削除前のオブジェクトの名前）が「TAB38B」であり、RECYCLEBIN NAME（リサイクル・ビン内での名前）が「BIN$FJYxlNyTDHzgU2Y4qMCU/Q==$0」であるオブジェクトがリサイクル・ビン内に格納されていることが確認出来ますね。
なるほど。リサイクル・ビンへ移動すると、元のオブジェクト名が自動的に変更されるということが理解出来ましたね。ちなみに、このオブジェクトはDROP TABLEする前は、表領域TBS38上に存在していましたが、リサイクル・ビンへ移動した今はどの表領域上に存在しているのでしょうか？って、「何だ、その子供じみた質問は？」と自分自身で思ってしまったのですが、答えは・・・「そのままTBS38表領域上です」ですね。実際に上記の回答例でも、DBA_SEGMENTSビューへ問い合わせることで、TBS38表領域上にあり、領域も消費したままの状態であることが確認出来ますね。もしも貴方がソフトウェアの開発者で、この機能を実装しようと考えた場合、Drop Table文でオブジェクトを別の表領域へ移動させようなんて思いますか？思いませんよね？だって、データを読書きする為に大量のディスクI/Oが発生して、CPUも消費するでしょう。そんな無駄なことを実装しょうとする人なんていないでしょう。
また、各種制約に関しても、その名称が変更されていることをUSER_CONSTRAINTSビューで確認することが出来ますが、お気付きの方はいらっしゃいますでしょうか？参照整合性制約が消えてしまっていますね。なんと。これは復旧時に影響が出てくるので、そこでまた説明させて頂きます。
はい。ここまでを整理すると、Drop Table文（正確には後で説明しますが、Purge句無し、初期化パラメータRECYCLEBIN=ONの場合）で表オブジェクトを削除すると、オブジェクト名が変更されるだけで元の表領域上に存在し続ける（領域を確保し続ける）。さらには、依存オブジェクト（索引）や制約も名前が変更されて保持されるが、一部の制約は完全に消失する。
ちなみに、リサイクル・ビン内のオブジェクトの一覧を確認するには、USER_RECYCLEBINビュー（シノニムRECYCLEBIN有り）、DBA_RECYCLEBINビューへ問い合わせることでも可能です。次は、USER_RECYCLEBINビューへの問合せ結果です。
$ sqlplus TRY/TRY
SQL>
-- リサイクル・ビン内のオブジェクトの確認
set serveroutput on
exec print_table('select * from USER_RECYCLEBIN') ;
OBJECT_NAME : BIN$FJYxlNyTDHzgU2Y4qMCU/Q==$0
ORIGINAL_NAME : TAB38B
OPERATION : DROP
TYPE : TABLE
TS_NAME : TBS38
CREATETIME : 2015-04-26:08:48:15
DROPTIME : 2015-04-26:08:51:38
DROPSCN : 1938607
PARTITION_NAME :
CAN_UNDROP : YES
CAN_PURGE : YES
RELATED : 14848
BASE_OBJECT : 14848
PURGE_OBJECT : 14848
SPACE : 4096
-----------------
OBJECT_NAME : BIN$FJYxlNySDHzgU2Y4qMCU/Q==$0
ORIGINAL_NAME : PK_TAB38B_COL1
OPERATION : DROP
TYPE : INDEX
TS_NAME : TBS38
CREATETIME : 2015-04-26:08:48:15
DROPTIME : 2015-04-26:08:51:38
DROPSCN : 1938603
PARTITION_NAME :
CAN_UNDROP : NO
CAN_PURGE : YES
RELATED : 14848
BASE_OBJECT : 14848
PURGE_OBJECT : 14849
SPACE : 64
-----------------
[参考] print_table：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1035431863958
3. Flashback Drop機能を使用して、TAB38b表をリサイクル・ビンからリストアして下さい。
$ sqlplus TRY/TRY
SQL>
-- 再度、リサイクル・ビン内を確認
show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------- ------------------------------ ------------ -------------------
TAB38B BIN$FJYxlNyTDHzgU2Y4qMCU/Q==$0 TABLE 2015-04-26:08:51:38
-- TAB38b表をリサイクル・ビンからリストア
FLASHBACK TABLE TAB38B TO BEFORE DROP ;
-- TAB38b表のレコード数を確認
select /*+ INDEX_FFS(T1) */ COUNT(*) from TAB38B T1;
COUNT(*)
----------
27776
-- TBS38表領域の使用量を確認
set linesize 120 pages 5000
col SEGMENT_NAME for a32
col TABLESPACE_NAME for a8
select TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME, sum(BYTES/1024/1024)
from DBA_SEGMENTS
where TABLESPACE_NAME='TBS38'
group by TABLESPACE_NAME, rollup(SEGMENT_TYPE, SEGMENT_NAME)
order by 1,2 ;
TABLESPA SEGMENT_TYPE SEGMENT_NAME SUM(BYTES/1024/1024)
-------- ------------- -------------------------------- --------------------
TBS38 INDEX BIN$FJYxlNySDHzgU2Y4qMCU/Q==$0 .5
TBS38 INDEX PK_TAB38A_COL1 .1875
TBS38 INDEX .6875
TBS38 TABLE TAB38A 8
TBS38 TABLE TAB38B 32
TBS38 TABLE 40
TBS38 40.6875
如何ですか？FLASHBACK TABLE文を実行するだけで、Drop Tableで削除した表が綺麗に復旧出来てしまいましたね！！レコード数も削除する前と完全に一致しています。ここまで簡単に戻せるとなれば、誤って表を削除されてしまっても、復旧出来そうな気がしてきましたよね。演習2で削除された表が別名で保持され続けている状態なのを確認している皆さんであれば、それほど難しいテクノロジーではない事も感じて頂けているのではないでしょうか。
とは言え、皆さんにはもう一歩進んだデータベース管理者を目指して頂きたいので、次の演習にチャレンジしていきましょう。
4. TAB38b表の依存オブジェクトや制約の状態を確認して下さい。
$ sqlplus TRY/TRY SQL> -- リサイクル・ビン内にオブジェクトが残っていないこと（全てリストア済み）を確認 select * from USER_RECYCLEBIN ; no rows selected -- TAB38b表の索引の名前を確認 select INDEX_NAME, TABLE_NAME from USER_INDEXES where TABLE_NAME='TAB38B' ; INDEX_NAME TABLE_NAME ------------------------------ -------------------------------- BIN$FJYxlNySDHzgU2Y4qMCU/Q==$0 TAB38B -- 各種制約の状態確認 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='TRY' order by 1,2,3 ; TABLE_ C CONSTRAINT_NAME STATUS VALIDATED SEARCH_CONDITION ------ - ------------------------------ -------- --------- ------------------ TAB38B P BIN$FJYxlNyRDHzgU2Y4qMCU/Q==$0 ENABLED VALIDATED TAB38B C BIN$FJYxlNyQDHzgU2Y4qMCU/Q==$0 ENABLED VALIDATED "COL1" IS NOT NULL
削除したTAB38b表をリストアしましたが、それに紐づいている索引オブジェクトや制約はどのような状態になっていましたか？
TAB38b表の索引は通常通りUSER_INDEXESビューで確認しますが、リサイクル・ビンへ移動した際に変更された名前のままになってしまっているではないですか。これは、まだリサイクル・ビンの中に残っているという意味では無く、Flashback Table文による表のリストア時に、索引オブジェクトの名前は元に戻らないことを意味しています。
さらに、TAB38b表に作成されていた制約は如何でしょう？こちらもリサイクル・ビンへ移動した際に変更された名前のままになっていますし、演習2の解説部分でも述べさせて頂いた、参照整合性制約が消えてしまったままですね。
さあ、データベース管理者の腕の見せ所です。張り切って元に戻していきましょう！
5. 演習4で確認したTAB38b表の依存オブジェクトや制約等を、削除前と同じ状態に戻して下さい。
$ sqlplus TRY/TRY SQL> -- TAB38b表の依存オブジェクトである主キーをPK_TAB38B_COL1へ名前変更 ALTER INDEX "BIN$FJYxlNySDHzgU2Y4qMCU/Q==$0" RENAME TO PK_TAB38B_COL1 ; Index altered. -- TAB38b表の索引を確認して、名前が変更されたことを確認 select INDEX_NAME, TABLE_NAME from USER_INDEXES where TABLE_NAME='TAB38B' ; INDEX_NAME TABLE_NAME ------------------------------ -------------------------------- PK_TAB38B_COL1 TAB38B -- 主キーとNot Null制約の名称を変更 alter table TAB38b rename constraints "BIN$FJYxlNyRDHzgU2Y4qMCU/Q==$0" to "PK_TAB38B_COL1" ; alter table TAB38b rename constraints "BIN$FJYxlNyQDHzgU2Y4qMCU/Q==$0" to "SYS_C004098" ; -- 参照整合性制約の追加 alter table TAB38b add constraint FK_TAB38a_COL1 FOREIGN KEY (COL3) references TAB38a(COL1) ; -- 各種制約の名称が変更されたことを確認 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='TRY' order by 1,2,3 ; TABLE_ C CONSTRAINT_NAME STATUS VALIDATED SEARCH_CONDITION ------ - --------------- -------- --------- ------------------ TAB38B P PK_TAB38B_COL1 ENABLED VALIDATED TAB38B C SYS_C004098 ENABLED VALIDATED "COL1" IS NOT NULL TAB38B R FK_TAB38A_COL1 ENABLED VALIDATED
とは言え、そんな難しいことでは無いですねぇ。索引に関しては、alter index文でrenameすれば良いだけですし、主キーとNot Null制約に関しても、alter table文でrenamesすれば良いだけです。はい、問題無いですね。
ただし、完全に消失してしまった参照整合性制約に関しては、alter table文で追加し直す必要がありますので、このような場合に備えて、DDL文をきちんと保管しておくことをお薦めします。
6. 改めて、TAB38a表とTAB38b表を両方削除して下さい。ただし、TAB38a表を削除する際には「purge句」を追加して下さい。
$ sqlplus TRY/TRY SQL> -- 初期化パラメータ"RECYCLEBIN"の設定値を確認 show parameter RECYCLEBIN NAME TYPE VALUE -------------------------- ----------- ----------------- recyclebin string on -- TAB38a表（purge句有り）とTAB38b表（purge句無し）の削除 drop table TAB38b ; drop table TAB38a purge ; -- リサイクル・ビン内を確認 show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ------------- ------------------------------ ------------ ------------------- TAB38B BIN$FJdHbtq+F/TgU2Y4qMBD7A==$0 TABLE 2015-04-26:10:09:19 -- TBS38表領域の使用量を確認 set linesize 120 pages 5000 col SEGMENT_NAME for a32 col TABLESPACE_NAME for a8 select TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME, sum(BYTES/1024/1024) from DBA_SEGMENTS where TABLESPACE_NAME='TBS38' group by TABLESPACE_NAME, rollup(SEGMENT_TYPE, SEGMENT_NAME) order by 1,2 ; TABLESPA SEGMENT_TYPE SEGMENT_NAME SUM(BYTES/1024/1024) -------- ------------ -------------------------------- -------------------- TBS38 INDEX BIN$FJdHbtq9F/TgU2Y4qMBD7A==$0 .5 TBS38 INDEX .5 TBS38 TABLE BIN$FJdHbtq+F/TgU2Y4qMBD7A==$0 32 TBS38 TABLE 32 TBS38 32.5
今更ながらの説明になりますが、Drop Table文を実行した際にpurge句を付けるか付けないかによって、リサイクル・ビンへ移動するか否かを選択することが可能です。上記の回答例で、purge句を付けて削除したTAB38a表は「show recyclebin」コマンドの結果に出力していないことや、DBA_SEGMENTSビューで確認しているTBB38表領域の使用量が40MBから約8MB減少していることからも、purge句を付けることでリサイクル・ビンへ移動しないで完全削除となる動作になることを理解して頂けると思いますね。
ちなみに、多くの方がご存知だとは思いますが、RECYCLEBINという初期化パラメータがあります。デフォルト「on」であり、Flashback Drop機能が有効化されています。つまり、purge句無しのDrop Table文で削除した表がリサイクル・ビンへ入る動作となっています。「RECYCLEBIN=off」に設定すれば、Flashback Drop機能が無効化され、purge句の有無に関わらず、Drop Table文で削除した表は完全に消失します。また、この初期化パラメータは、インスタンス・レベル、セッション・レベルへ動的に変更することが可能です。
7. 索引オブジェクトをリサイクル・ビンから削除し、TBS38表領域の記憶域を解放して下さい。
$ sqlplus TRY/TRY SQL> -- 索引オブジェクト"BIN$FJdHbtq9F/TgU2Y4qMBD7A==$0"をリサイクル・ビンから削除を試行 purge index "BIN$FJdHbtq9F/TgU2Y4qMBD7A==$0" ; * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-02429: cannot drop index used for enforcement of unique/primary key -- 索引オブジェクト"BIN$FJdHbtq9F/TgU2Y4qMBD7A==$0"の -- USER_RECYCLEBIN.CAN_PURGEを確認 select ORIGINAL_NAME, OBJECT_NAME, TYPE, CAN_PURGE from USER_RECYCLEBIN where OBJECT_NAME='BIN$FJdHbtq9F/TgU2Y4qMBD7A==$0' ; ORIGINAL_NAME OBJECT_NAME TYPE CAN ---------------------- ------------------------------ ------ --- PK_TAB38B_COL1 BIN$FJdHbtq9F/TgU2Y4qMBD7A==$0 INDEX YES -- 元TAB38b表（BIN$FJdHbtq+F/TgU2Y4qMBD7A==$0）に紐づく制約の確認 col OWNER for a8 col TABLE_NAME for a32 col CONSTRAINT_NAME for a32 col SEARCH_CONDITION for a20 select TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, STATUS, VALIDATED from USER_CONSTRAINTS where TABLE_NAME='BIN$FJdHbtq+F/TgU2Y4qMBD7A==$0' and OWNER='TRY' order by 1,2,3 ; TABLE_NAME C CONSTRAINT_NAME STATUS VALIDATED ------------------------------ - ------------------------------ ------- --------- BIN$FJdHbtq+F/TgU2Y4qMBD7A==$0 C BIN$FJdHbtq7F/TgU2Y4qMBD7A==$0 ENABLED VALIDATED BIN$FJdHbtq+F/TgU2Y4qMBD7A==$0 P BIN$FJdHbtq8F/TgU2Y4qMBD7A==$0 ENABLED VALIDATED -- 主キー制約を削除 alter table "BIN$FJdHbtq+F/TgU2Y4qMBD7A==$0" drop constraint "BIN$FJdHbtq8F/TgU2Y4qMBD7A==$0" ; * ERROR at line 1: ORA-38301: can not perform DDL/DML over objects in Recycle Bin
本気で取り組んでしまわれた方、大変失礼致しまいた。リサイクル・ビン内のオブジェクトに対する操作が限定されていることを体験して頂きたく、実現できないイジワルな演習問題になっていたからです。とは言え、完全に身に着きましたよね？
リサイクル・ビン内のオブジェクトを完全に消して表領域の領域を解放する為には、パージ処理が必要となります。皆さんのPCのゴミ箱機能にも「ゴミ箱を空にする」的な機能であったり、個別ファイル毎に完全にゴミ箱から消す機能がありますよね。それに似ていますね。
まずは、索引オブジェクトをパージする為、purge index文を発行していますが、ORAエラーが発生してしまいました。エラーメッセージから明らかな通り、「ユニーク・キーや主キーの索引はパージできない」という制限が体験出来たことになります。であれば、そんな制約を消してしまえばいいじゃないか！と思って、主キーを消そうとalter table drop constraint文を試行してみましたが、残念ながらこれまたORAエラーが発生してしまいました。エラーメッセージとしては、「リサイクル・ビン内のオブジェクトに対するDDL文やDML文は実行できない！」という制限が体験出来ましたね。
と言う事で、主キーを実現している索引オブジェクトを個別にリサイクル・ビンから削除することは出来ないということですね。まあ、表単位でパージ処理すれば良いだけですけどね。はい。でも、こういう細かい部分を知っていることが他者との差別化になるとも思っています。
8. SYSユーザーでリサイクル・ビンから全てのオブジェクトを消去して下さい。
$ sqlplus / as sysdba SQL> -- DBA_RECYCLEBINで、システム全体のリサイクル・ビン内のオブジェクトを確認 col ORIGINAL_NAME for a16 col TYPE for a16 select OWNER, ORIGINAL_NAME, OBJECT_NAME, TYPE from DBA_RECYCLEBIN order by 1, 4, 2 ; OWNER ORIGINAL_NAME OBJECT_NAME TYPE -------- ---------------- ------------------------------ ----- TRY PK_TAB38B_COL1 BIN$FJdHbtq9F/TgU2Y4qMBD7A==$0 INDEX TRY TAB38B BIN$FJdHbtq+F/TgU2Y4qMBD7A==$0 TABLE -- 現行ユーザー(SYS)のリサイクル・ビンから全てのオブジェクトを消去 purge recyclebin ; -- 再度、DBA_RECYCLEBINで、システム全体のリサイクル・ビン内のオブジェクトを確認 --> 消去されていない。。。 select OWNER, ORIGINAL_NAME, OBJECT_NAME, TYPE from DBA_RECYCLEBIN order by 1, 4, 2 ; OWNER ORIGINAL_NAME OBJECT_NAME TYPE -------- ---------------- ------------------------------ ----- TRY PK_TAB38B_COL1 BIN$FJdHbtq9F/TgU2Y4qMBD7A==$0 INDEX TRY TAB38B BIN$FJdHbtq+F/TgU2Y4qMBD7A==$0 TABLE -- システム全体のリサイクル・ビンから全てのオブジェクトを消去 purge dba_recyclebin ; -- 再度、DBA_RECYCLEBINで、システム全体のリサイクル・ビン内のオブジェクトを確認 --> 消去されている。 select OWNER, ORIGINAL_NAME, OBJECT_NAME, TYPE from DBA_RECYCLEBIN order by 1, 4, 2 ; no rows selected
はいはい。まだまだ簡単には終わらせませんよ。お付き合い下さいね。
この演習は、削除した表オブジェクトがTRYスキーマのものであるにも関わらず、SYSユーザーでパージ処理をしなさい。という部分が肝です。と言うのも、リサイクル・ビンから全てのオブジェクトを消去するコマンドは「purge recyclebin;」ですが、この「全ての」という表現が難しい所なのです。正確には、「現行ユーザー（そのセッションに接続しているユーザー）の全てのオブジェクト」であり、いくらSYSユーザーであっても「purge recyclebin;」コマンドでは、他のユーザーのオブジェクトをパージすることが出来ないのです。SYSユーザーで「purge recyclebin;」コマンドを実行したので、データベース全体でリサイクル・ビンの中を空にしたと思い込んでいるデータベース管理者の方と出会ったので、この演習問題を作成させて頂きましたので、是非注意して下さいね。
なので、TRYユーザーで接続し直せば良いと思うのですが、それはそれでユーザーの種類が多ければ、面倒な作業になるでしょう。と言う所で、SYSユーザーからデータベース全体でリサイクル・ビンの中を空にするコマンドが別に存在するのですね。それが「purge dba_recyclebin ;」コマンドなのです！！
では、最後にちょっと気を付けて頂きたい、データベースの性能問題を体験して頂きたいと思います。
ここまでご紹介してきたFlashback Drop機能を使用することにより、Drop Table文を実行しても名前が変更されてリサイクル・ビンへ移動するだけであり、表領域の使用量は解放されないということを理解して頂けていると思います。しかし、いつまでもパージ処理をしなかった場合は、どこまで保持され続けてしまうのでしょうか？表領域が削除済みのオブジェクトで一杯になってしまうのでしょうか？もし、そのような場合には、新しいオブジェクトを作成する空き領域が無いと言う事になってしまい、ORAエラーが発生したりしないのか？色々疑問が出てきますよね。さあ、次の演習にチャレンジしてみてください。
9. バッチ処理で中間表のCreate & Dropを毎晩繰り返すシナリオを想定し、TAB38a表のCreate & INSERT & Dropを200回繰り返すことで、INSERT処理時間がどのように変化するのかを確認してみてください。
$ for i in `seq 1 200`; do
echo "
select TABLESPACE_NAME, count(SEGMENT_NAME), sum(BYTES)/1024/1024 from dba_segments where TABLESPACE_NAME='TBS38' group by TABLESPACE_NAME ;
create table TAB38a_${i} (COL1 number NOT NULL, COL2 char(1000)) ;
set timing on
insert into TAB38a_${i} select LEVEL, 'A'||to_char(LEVEL) from DUAL connect by LEVEL <=7*128*7 ;
set timing off
commit ;
create unique index PK_TAB38a_${i} on TAB38a_${i}(COL1) ;
alter table TAB38a_${i} add primary key (COL1) using index PK_TAB38a_${i} ;
drop table TAB38a_${i} ;"
done > createTable_200loop.sql
$ sqlplus TRY/TRY
SQL>
spool createTable_200loop.log
@createTable_200loop.sql
spool off
上記の回答例は、TAB38a表のCreate & INSERT & Dropを200回繰り返しています。その過程でTBS38表領域上のオブジェクト数と使用量、さらには、INSERT処理時間を監視した結果が次のグラフとなります。性能値に関してはあくまで傾向を掴む為の参考として提示させて頂いていますので、この数字をどの環境でも担保するものではありませんのでご注意ください。
横軸が表のCreate、INSERTとDrop処理を繰り返した回数ですが、回数を重ねるごとに、青色（TBS38表領域の使用量[MB]）と緑色（リサイクル・ビン内のオブジェクト数[個]）が綺麗に右肩上がりであることが確認出来ますね。そして、黒い縦の点線（180回前後）でこれらの青色と緑色の線は頭打ちになり、一定数を推移していることも確認できます。これは何を意味しているのでしょうか？
青色の線は、TBS38表領域の使用量[MB]を表していますが、頭打ちになっている値は、約1500MBですね。おや？演習1でTBS38表領域を作成して頂いた時に指定したデータファイルのサイズと同じ値ですね。つまりは、表領域が一杯になるまでリサイクル・ビン内のオブジェクトを保持するという動きがこのグラフから読み取れると言う事になります。ただし、表領域が一杯になっても、INSERT処理で領域不足のORAエラーは発生しなかったのも事実なので、新規の表を作成してINSERTする領域としては使いまわせていることも意味します。なるほどーですね。これで、いつまでもリサイクル・ビン内のオブジェクトをパージしなくても、表領域の空き領域観点では問題が無いことが理解出来ましたね。
とは言え、一番目立つ色である赤色の線（INSERT処理時間[秒]）の推移を確認してみてください。黒い縦の点線（180回前後）の前後において、INSERT処理時間の傾向が大きく変化していることにお気付きでしょうか？はい。残念ながらINSERT処理時間が劣化してしまっています。何故、このような現象が発生しているのかは簡単に推測出来ますよね。黒い縦の点線よりも前はTBS38表領域に空き領域があるので、新規にINSERTされるデータを格納する領域を直ぐに確保できていましたが、黒い縦の点線よりも後は、リサイクル・ビン内のオブジェクトによってTBS38表領域が一杯になってしまったので、新規にINSERTするデータを格納する空き領域を作り出す必要が出てきたからですね。具体的な待機イベントに関しては、是非とも、第29回 UNDO表領域の管理～パフォーマンス・チューニング～でご紹介したASH（Active Session History）やAWRを使用して分析してみてくださいね。
つまりは、いつまでもリサイクル・ビン内のオブジェクトをパージしなくても領域枯渇を示すORAエラーは発生しませんが、新たなデータを格納しようとする際に、オーバーヘッドが生じる可能性があると言う事なので、不要なオブジェクトは直ぐにパージするクセを付けたり、定期的なパージ処理を実装する等の運用をご検討下さいね。
さて、Flashback Drop機能による削除表の復旧と注意点は如何でしたでしょうか？
例えば、バッチ処理が想定外の挙動をしてしまい、誤って表が削除されてしまった場合にFlashback Table文で表を復旧させること可能とするFlashback Drop機能は本当に便利な機能です。私も本番環境では無いですが、テスト環境においてINSERTに長時間を要する表を誤って削除してしまったことがあり、スケジュール的にやり直しができない窮地にたたされたことがありました。その際に私を救ってくれたのは、このFlashback Drop機能であったのです。デフォルトで有効化されていますので、知らないうちに使用している機能の一つだと思いますが、少々クセのある機能ですので特にパージ処理の運用には充分配慮して頂きたいと思います。
と言う事で、今回も最後まで体験して頂きましてありがとうございました。是非、感想や質問をお待ちしておりますね。次回以降もどうぞよろしくお願い致します。