概要
Oracle にて 特定の日時のデータを取得するSQL
ある時点のデータを取得する
SELECT * FROM hoge
as of timestamp to_timestamp('2023-9-11 13:14:00' , 'yyyy-mm-dd hh24:mi:ss');
更新履歴をみる
調査したい行は特定できるが，上記のタイムスタンプでいちいち変更履歴を追っかけるのは大変な場合。
SELECT versions_xid, versions_starttime,
versions_operation,T_HOGE.*
FROM T_HOGE
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2023-07-28 17:10:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2023-07-28 17:15:00', 'YYYY-MM-DD HH24:MI:SS')
where 行を絞り込む条件を記入する;
versions_xid, versions_starttime,versions_operation は予約語です。変更された日時などが表示される。
versions_operation
U:更新された
I：追加された
という意味です
備考
過去どこまでさかのぼれるか
設定されている保存日数を確認するSQL。単位は秒
show parameter undo_retention
ただし実際に保存されているかどうかは別問題。表を消してパージしている場合など。また，大型のトランザクションがある場合も，メインの処理を優先するためにUNDO領域への書き込みをしないことがある（「UNDO保存の保証」 設定変更で可能。ただしパフォーマンスに影響する。）
UNDO保存の保証 設定内容を確認する
SELECT tablespace_name, contents, retention FROM dba_tablespaces;
CONTENTS が UNDO となっている行の RETENTION 値をみる
NOGUARANTEE： 非保証（必要に応じて上書きしてエラーが起きないようにする）
GUARANTEE：保証（保存期間内のものは上書きしない。ただし，大規模更新の場合 ORA-30036:UNDO表領域内でセグメントを拡張できません が発生する可能性あり。Oracleサイトでは”警告:保存期間の保証を有効にすると、複数のDML操作が失敗する可能性があります。この機能は注意して使用してください。” と書かれている
保存期間 undo_retention の値変更方法
初期化パラメーターを変更する。DBの再起動は不要。単位は秒
alter system set "undo_retention"= 2592000 scope=both sid='*';
2592000 は 30日間
表領域 UNDOTBS1 が自動拡張かどうか
SELECT TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE FROM DBA_DATA_FILES ORDER BY 1
AUTOEXTENSIBLE が YES であれば自動拡張
自動UNDO管理 が有効の場合
UNDO表領域サイズとシステム・アクティビティに基づいて、UNDO保存期間は自動的にチューニングされます。UNDO_RETENTION初期化パラメータを設定していたとしても，UNDO表領域の空き容量が小さい場合，（自動拡張の表領域なら）表領域の自動拡張がなされるようですが，何らかの理由でむつかしい場合，保存期間が自動チューニングされUNDO_RETENTION値よりも小さくなります。
結局，現在の UNDO保存期間 がどうなっているかは 以下のSQLで調査できます。
select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time, to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention from v$undostat order by end_time desc;
10分おきにチューニングされるそうなので，10分後に確認するといい
保存期間を増やしたい場合
・初期化パラメータ UNDO_RETENTION を 増やす
・UNDO表領域が自動拡張になっていても，手動でサイズを大きくして空き容量を増やす
プロパティ
Oracle 12.2，19.3, 19.8
コメント