Flashback

Flashback version query (as of) 

Работает через UNDO, не требует включения flashback в базе
SELECT * FROM par; ID TXT ---------- ---------- 1 doc 1 2 doc 2 3 today SELECT * FROM par AS OF TIMESTAMP to_date('21.08.2013 22:55:00', 'DD.MM.YYYY HH24:MI:SS'); ID TXT ---------- ---------- 1 doc 1 2 doc 2 
Вариант с датой дает 3 секундную погрешность. Есть вариант работы через scn -- Такой функцией можно узнать scn по времени с погрешностью SELECT timestamp_to_scn( to_date('21.08.2013 22:55:00', 'DD.MM.YYYY HH24:MI:SS')) FROM dual; TIMESTAMP_TO_SCN(TO_DATE('21.0 ------------------------------ 4192406 SELECT * FROM par AS OF SCN 4192406; ID TXT ---------- ---------- 1 doc 1 2 doc 2 Flashback version query (versions between) Пишет историю жизни строк в течении UNDO_RETENTION. Если нижняя граница раньше, sysdate - UNDO_RETENTION, то будет ошибка ORA-30052 INSERT INTO par VALUES (4, 'doc 4'); COMMIT; UPDATE par p SET p.txt = 'new value' WHERE ID = 4; COMMIT; SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, par.* FROM par VERSIONS BETWEEN TIMESTAMP SYSDATE - 1/24/60 * 15 AND SYSDATE; VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION ID TXT ----------------- ------------------------------------------------- --------------- ------------------------------------------------- ---------------- ------------------ ---------- ---------- 4234414 22.08.13 13:37:30 03001B001E0F0000 U 4 new value 4234333 22.08.13 13:34:51 4234414 22.08.13 13:37:30 0A002000A20B0000 I 4 doc 4 1 doc 1 2 doc 2 3 today SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, par.* FROM par VERSIONS BETWEEN TIMESTAMP SYSDATE - 1/24 AND SYSDATE ORA-30052: недопустимое выражение для нижней границы снимков Что бы увидеть все изменения, можно использовать конструкции MINVALUE и MAXVALUE SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, par.* FROM par VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE; Работает по UNDO, не требует включения flashback для БД flashback_transaction_query Пишет изменения из UNDO. Одна из самых полезных фич -- колонка undo_sql. SELECT * FROM flashback_transaction_query WHERE table_name = 'PAR'; XID START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL ---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- -------------------------------------------------------------------------------- 0100020071D50000 40244472 22.08.2013 13:4 40244474 22.08.2013 13:43 SYSTEM 1 UPDATE PAR SYSTEM AAAiW3AABAAAZr6AAA update "SYSTEM"."PAR" set "TXT" = 'doc 4' where ROWID = 'AAAiW3AABAAAZr6AAA'; 0200190091C10000 40244474 22.08.2013 13:4 40244496 22.08.2013 13:44 SYSTEM 1 INSERT PAR SYSTEM AAAiW3AABAAAZr6AAB delete from "SYSTEM"."PAR" where ROWID = 'AAAiW3AABAAAZr6AAB'; 03000B00E9E50000 40244496 22.08.2013 13:4 40244498 22.08.2013 13:44 SYSTEM 1 UPDATE PAR SYSTEM AAAiW3AABAAAZr6AAB update "SYSTEM"."PAR" set "TXT" = 'doc 1' where ROWID = 'AAAiW3AABAAAZr6AAB'; 0A0003001ECD0000 40244417 22.08.2013 13:4 40244472 22.08.2013 13:43 SYSTEM 1 INSERT PAR SYSTEM AAAiW3AABAAAZr6AAA delete from "SYSTEM"."PAR" where ROWID = 'AAAiW3AABAAAZr6AAA'; На Oracle 12C с pluggable database таблица почему-то не заполняется SQL> alter session set container=db1; Session altered SQL> create table tbl(id number); Table created SQL> insert into tbl values (1); 1 row inserted SQL> commit; Commit complete SQL> SELECT * FROM flashback_transaction_query; SELECT * FROM flashback_transaction_query ORA-01295: несоответствие DB_ID между словарем USE_ONLINE_CATALOG и файлами журналов SQL> alter session set container=CDB$ROOT; Session altered SQL> SELECT * FROM flashback_transaction_query; XID START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL ---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- -------------------------------------------------------------------------------- SQL> Flashback archive (oracle total recall) Фича позволяет обойти ограничения с UNDO для запросов Flashback version query. Создатет несколько дополнительных табличек, в которые записывает транзакции и изменения. Flashback version query после этого работает в течении интервала, заданного в retention при создании flashback archive -- Создаем архив, указываем где храним и сколько храним. -- Можно добавлять tablespace, указывать квоты, чистить SQL> create flashback archive default fa tablespace users retention 1 year; Done -- При создании таблицы указываем, что хранить для нее архив SQL> create table tbl(a number) FLASHBACK ARCHIVE; Table created SQL> insert into tbl values(1); 1 row inserted SQL> commit; Commit complete SQL> update tbl set a = 2; 1 row updated SQL> commit; Commit complete SQL> delete from tbl; 1 row deleted SQL> commit; Commit complete -- Помимо того, что можно делать SELECT ... AS OF ... еще создаются таблички в которые можно поглядеть SQL> SQL> SELECT * FROM SYS_FBA_HIST_101864; SELECT * FROM SYS_FBA_HIST_101864; RID STARTSCN ENDSCN XID OPERATION A -------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ---------- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I 1 AAAY3oAABAAAa/ZAAA 30076162 30076162 08001C00D0680000 D 2 AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U 2 SQL> SELECT * FROM SYS_MFBA_NHIST_101864; SELECT * FROM SYS_MFBA_NHIST_101864; RID STARTSCN ENDSCN XID OPERATION A -------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ---------- SQL> SELECT * FROM SYS_FBA_TCRV_101864; SELECT * FROM SYS_FBA_TCRV_101864; RID STARTSCN ENDSCN XID OP -------------------------------------------------------------------------------- ---------- ---------- ---------------- -- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U -- Попробуем изменить структуру. После него SELECT AS OF не заработал, одна созданная табличка пропала SQL> alter table tbl add b varchar2(100); Table altered SQL> insert into tbl values (1, 'txt 1'); 1 row inserted SQL> commit; Commit complete SQL> update tbl set b = 'new txt'; 1 row updated SQL> commit; Commit complete SQL> SQL> SELECT * FROM SYS_FBA_HIST_101864; SELECT * FROM SYS_FBA_HIST_101864; RID STARTSCN ENDSCN XID OPERATION A B -------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ---------- -------------------------------------------------------------------------------- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I 1 AAAY3oAABAAAa/ZAAA 30076162 30076162 08001C00D0680000 D 2 AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U 2 SQL> SELECT * FROM SYS_MFBA_NHIST_101864; SELECT * FROM SYS_MFBA_NHIST_101864; SELECT * FROM SYS_MFBA_NHIST_101864 ORA-00942: таблица или представление пользователя не существует SQL> SELECT * FROM SYS_FBA_TCRV_101864; SELECT * FROM SYS_FBA_TCRV_101864; RID STARTSCN ENDSCN XID OP -------------------------------------------------------------------------------- ---------- ---------- ---------------- -- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U SQL> SQL> SELECT * FROM SYS_FBA_HIST_101864; SELECT * FROM SYS_FBA_HIST_101864; RID STARTSCN ENDSCN XID OPERATION A B -------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ---------- -------------------------------------------------------------------------------- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I 1 AAAY3oAABAAAa/ZAAA 30076162 30076162 08001C00D0680000 D 2 AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U 2 SQL> SELECT * FROM SYS_FBA_DDL_COLMAP_101864; SELECT * FROM SYS_FBA_DDL_COLMAP_101864; STARTSCN ENDSCN XID OPERATION COLUMN_NAME TYPE HISTORICAL_COLUMN_NAME ---------- ---------- ---------------- --------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 30076127 A NUMBER A 30076535 B VARCHAR2(100) B SQL> SELECT * FROM SYS_FBA_TCRV_101864; SELECT * FROM SYS_FBA_TCRV_101864; RID STARTSCN ENDSCN XID OP -------------------------------------------------------------------------------- ---------- ---------- ---------------- -- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U SQL> Не работает под пользователем с привелегией DBA (system) create flashback archive default fa tablespace users retention 1 year ORA-55611: Нет полномочий для управления архивом Flashback по умолчанию Не работает для pluggable database в 12С create flashback archive default fa tablespace users retention 1 year ORA-65131: Функция Flashback Data Archive не поддерживается для подключаемой базы данных. Документация