UNDOに関する検証 その３
＜UNDOに関する検証その３＞
ペンネーム：クレイジーボーダー
前回は、UNDO表領域の切替えを行った。UNDO表領域変更中に実行されていた
トランザクションは、引き続き変更前のUNDO表領域を使用する。また、その
UNDO表領域はONLINEモードからPENDING OFFLINEモードに変わる。そのため、
変更前の表領域は使用中のために削除できない。
SQL> DROP TABLESPACE UNDOTBS_02; DROP TABLESPACE UNDOTBS_02 * ERROR at line 1: ORA-30013: undo tablespace 'UNDOTBS_02' is currently in use
全てのトランザクションがコミットされた後は、自動的にOFFLINEになり、
削除できる。ここまでが前回の内容だが、ここで疑問が１つでてきた。
初期化パラメータ(UNDO_RETENTION)との関係だ。UNDO_RETENTIONとは、UNDO
データを保存する期間を秒単位で指定するものであり、デフォルト値は15分
（900秒）であるが、削除されるタイミングが UNDO_RETENTION で設定した期
間内ならどうなるだろうか？
今回は、フラッシュバック問合せを使用して検証してみる。フラッシュバック
問合せの詳細については過去のメルマガを参照して欲しい。
まず、現在のUNDOに関連した初期化パラメータは以下のようになっている。
SQL> SHOW PARAMETER UNDO NAME TYPE VALUE ------------------------------------ ----------- ------------------ UNDO_MANAGEMENT STRING AUTO UNDO_RETENTION INTEGER 10800 UNDO_SUPPRESS_ERRORS BOOLEAN FALSE UNDO_TABLESPACE STRING UNDOTBS_02
UNDOデータの保存期間は、デフォルトでは15分しかないので3時間（10800秒）
に変更し起動させた。
それでは、フラッシュバック問合せが使用できるよう、SYSユーザで
DBMS_FLASHBACKの実行権限をMAGに与える。
＜ユーザSYSのSQL文＞
SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO MAG;
ユーザMAGの表（TBL_MAG1）の行数を確認する。
＜ユーザMAGのSQL文＞
SQL> SELECT TO_CHAR(SYSDATE, 'HH24:MI') AS TIME, COUNT(*)
FROM TBL_MAG1;
TIME COUNT(*)
----- ----------
13:10 11
現在アクティブなトランザクションは実行されていない。別ターミナルから
ユーザMAGで、insert文を実行する。
＜ユーザMAGのSQL文＞
SQL> INSERT INTO TBL_MAG1 VALUES (134, 'MAG_0128'); 1 ROW CREATED.
ここで現在UNDOセグメントの使用状況を見てみる。
＜ユーザSYSのSQL文＞
SQL> SELECT S.USERNAME, T.XIDUSN, T.UBAFIL, T.UBABLK, T.USED_UBLK, 2 RN.NAME AS "SEGMENT", RS.STATUS, RS.EXTENTS, RS.RSSIZE, 3 RS.HWMSIZE, RS.XACTS, DF.STATUS AS "FILE STATUS", 4 DF.NAME AS "FILE", TS.NAME AS "TABLESPACE" 5 FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME RN, V$ROLLSTAT RS, 6 V$DATAFILE DF, V$TABLESPACE TS 7 WHERE S.SADDR = T.SES_ADDR 8 AND T.XIDUSN = RN.USN 9 AND RN.USN = RS.USN 10 AND T.UBAFIL = DF.FILE# 11 AND TS.TS# = DF.TS#; USERNAME XIDUSN UBAFIL UBABLK USED_UBLK SEGMENT STATUS -------- ------ ------ ------ --------- ---------- ------ MAG 16 5 355 1 _SYSSMU16$ ONLINE EXTENTS RSSIZE HWMSIZE XACTS FILE STATUS ------- ------ ------- ----- ----------- 2 129024 129024 1 ONLINE FILE TABLESPACE --------------------------------------------- ---------- /export/home/mag920/oradata/mag920/undo02.dbf UNDOTBS_02
UNDOセグメントを使用しているようなので、ユーザMAGでコミットを行なう。
＜ユーザMAGのSQL文＞
SQL> COMMIT;
COMMIT COMPLETE.
SQL> SELECT COUNT(*) FROM TBL_MAG1;
COUNT(*)
----------
12
再度、ユーザSYSで、先ほどUNDO情報を取得したSQL文を実行すると、コミット
後なので、何も選択されない。
＜ユーザSYSのSQL文＞
SQL> SELECT S.USERNAME, T.XIDUSN, T.UBAFIL, T.UBABLK, T.USED_UBLK, 2 RN.NAME, RS.STATUS, RS.EXTENTS, RS.RSSIZE, RS.HWMSIZE, RS.XACTS, 3 DF.STATUS AS "FILE STATUS", DF.NAME AS "FILE", 4 TS.NAME AS "TABLESPACE" 5 FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME RN, V$ROLLSTAT RS, 6 V$DATAFILE DF, V$TABLESPACE TS 7 WHERE S.SADDR = T.SES_ADDR 8 AND T.XIDUSN = RN.USN 9 AND RN.USN = RS.USN 10 AND T.UBAFIL = DF.FILE# 11 AND TS.TS# = DF.TS#; NO ROWS SELECTED
ユーザMAGでフラッシュバック問合せを利用し、過去のデータを取得できるこ
とを確認する。
＜ユーザMAGのSQL文＞
時間指定を簡単に行なう為、NLS_TIMESTAMP_FORMATを変更する。
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
コミット前のデータを取得してみる。
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME('2003/01/28 13:10:00');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT COUNT(*) FROM TBL_MAG1;
COUNT(*)
----------
11
SQL> EXECUTE DBMS_FLASHBACK.DISABLE;
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
UNDOデータの保存期間内であることを確認し、UNDO表領域を変更し削除する。
SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS_01'; SYSTEM ALTERED. SQL> DROP TABLESPACE UNDOTBS_02; TABLESPACE DROPPED.
UNDO_RETENTIONで設定した期間内でも削除することができた。
UNDO表領域は削除されてしまい、データが無い状態になった。確認としてユー
ザMAGでフラッシュバック問合せを実行する。データがデータベース・バッファ
から追い出された後から実行すると以下の結果となる。
＜ユーザMAGのSQL文＞
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME('2003/01/28 13:10:00');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT COUNT(*) FROM TBL_MAG1;
SELECT COUNT(*) FROM TBL_MAG1
ERROR AT LINE 1:
ORA-01555: SNAPSHOT TOO OLD: ROLLBACK SEGMENT NUMBER WITH NAME ""
TOO SMALL
UNDOデータが無いために、ORA-01555のエラーが出力されたのである。
まとめると、UNDO表領域の削除は、UNDO_RETENTIONで設定した期間内でも削除
できてしまい、フラッシュバック問合せができなくなる。
要するに削除する際は注意が必要である。
次回は、V$UNDOSTATを使用してこのUNDO_RETENTIONについて検証していきたい。
以上、雪がなかなか降らない茅ヶ崎にて