UNDOに関する検証 その3

<UNDOに関する検証その3>
ペンネーム:クレイジーボーダー

前回は、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になり、
削除できる。ここまでが前回の内容だが、ここで疑問が1つでてきた。
初期化パラメータ(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について検証していきたい。

以上、雪がなかなか降らない茅ヶ崎にて