Обновление данных в таблицах

Данные о номере последней транзакции, изменившей строку таблицы

В версии 10 стало возможным с помощью системной переменной ("псевдостолбца") ORA_ROWSCN узнать SCN транзакции, внесшей последнее изменение в строку. Если ничего не предпринимать специально, то этот номер будет приближенный и соответствовать фактически не строке, а блоку, в котором хранится в БД строка. (Сама возможность хранить вместе со строкой SCN ее последней правки существовала и раньше и использовалась для параллельной репликации, но смотреть этот номер в программе было нельзя).

Пример:

SQL> CREATE TABLE dscn AS SELECT * FROM dept;
Table created.
SQL> UPDATE dscn SET dname = LOWER ( dname ) WHERE ROWNUM <= 2;
2 rows updated.
SQL> SELECT dname, ora_rowscn FROM dscn;
DNAME          ORA_ROWSCN
-------------- ----------
accounting        3812962
research          3812962
SALES             3812962
OPERATIONS        3812962
SQL> COMMIT;
Commit complete.
SQL> SELECT dname, ora_rowscn FROM dscn;
DNAME          ORA_ROWSCN
-------------- ----------
accounting        3812999
research          3812999
SALES             3812999
OPERATIONS        3812999

Однако если создать таблицу с особым указанием, в ней появится скрытый столбец (длиною 6 байт), рассчитанный на хранение номера SCN индивидуально для каждой строки:

SQL> DROP TABLE dscn;
Table dropped.
SQL> CREATE TABLE dscn ROWDEPENDENCIES AS SELECT * FROM dept;
Table created.
SQL> UPDATE dscn SET dname = LOWER ( dname ) WHERE ROWNUM <= 2;
2 rows updated.
SQL> SELECT dname, ora_rowscn FROM dscn;
DNAME          ORA_ROWSCN
-------------- ----------
accounting
research
SALES             3814027
OPERATIONS        3814027
SQL> COMMIT;
Commit complete.
SQL> SELECT dname, ora_rowscn FROM dscn;
DNAME          ORA_ROWSCN
-------------- ----------
accounting        3814035
research          3814035
SALES             3814027
OPERATIONS        3814027

Эти данные можно использовать в программе, чтобы определить, изменялась ли строка с какого-то времени. Перевести SCN в астрономическое время (приближенно) можно обращением к специальной функции, например:

SELECT dname, SCN_TO_TIMESTAMP ( ora_rowscn ) FROM dscn;

Обращение с прошлыми данными после внесения изменений

Традиционно все существующие СУБД создавались как средства моделирования текущего состояния предметной области. Выдача COMMIT переводит БД в новое состояние, и предыдущие данные оказываются потерянными. Такое поведение проще программировать разработчикам СУБД, но оно не всегда удобно пользователям. В частности:

• для восстановления данных, потерянных в результате непродуманной выдачи COMMIT, пусть даже совсем недавно, приходилось довольствоваться восстановлением по резервной копии БД, что долго и требует наличия собственно резервной копии;
• нередко возникающую потребность моделировать историю изменения данных приходится имитировать разработчику приложения на свое усмотрение.

В версии 9 в Oracle открылись возможности получать от СУБД значения данных таблицы по состоянию на прошлый момент, невзирая на осуществлявшиеся за это время операции фиксации транзакций. В версии 10 эти возможности получили свое развитие. Техническая основа у них разная: использование временно сохранившихся данных в табличном пространстве типа UNDO; использование свободного места в табличном пространстве БД с данными таблицы; особый способ журнализации данных. Отсюда проистекают различия в доступных сроках давности для восстановления в разных случаях.

Обращение к прошлым значениям данных в таблице

Для (быстрого) запроса к прежним данным ссылку на таблицу во фразе FROM предложения SELECT следует сопроводить указанием конструкции AS OF.

Пример (выполнить в качестве упражнения последовательно):

DELETE FROM emp;
COMMIT;
SELECT * FROM emp;
INSERT INTO emp 
 SELECT *
 FROM   emp AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '1' MINUTE )
; 
SELECT * FROM emp;

(Синтаксис допускает употребление скобок в запросе выше, но не требует этого).

Момент для восстановления можно еще указать в терминах номера изменений данных в БД (System Change Number, SCN).

Подобное извлечение старых значений возможно только за период, когда к таблице не применялись команды DDL.

Давность воспроизводимых данных ограничивается размером свободного места в табличном пространстве UNDO, которое определяется (а) интенсивностью изменений БД и (б) полным размером пространства.

В версии 10 появилась возможность вместо AS OF уточнять имя таблицы во фразе FROM конструкцией VERSIONS BETWEEN, позволяющей извлекать из БД историю изменения строк определенной давности. Следующим примером можно продолжить приводившийся только что код:

COLUMN versions_endtime FORMAT A22
COLUMN versions_starttime FORMAT A22
SELECT 
  empno
, sal
, versions_starttime
, versions_endtime
, versions_xid
, versions_operation 
FROM
emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
;

Прочие поля строк таблицы EMP в этом запросе не выданы из экономии места.

Упражнение: Измените несколько раз зарплаты разным сотрудникам и просмотрите историю изменений.

Восстановление данных существующих таблиц, ранее удаленных таблиц и всей БД

С версии 10 открылась возможность одной командой восстановить строки таблицы на момент в прошлом. Это позволяет вместо вышеуказанного INSERT … SELECT … AS OF записать

FLASHBACK TABLE emp 
TO TIMESTAMP ( SYSTIMESTAMP - INTERVAL '1' MINUTE )
;

Тем не менее:

• эти две команды не равносильны, когда перед восстановлением строк таблица была непуста;
• чтобы восстановление строк таблицы командой FLASHBACK было возможным, мы должны разрешить системе изменять физические адреса строк таблицы. Например, выдать: ALTER TABLE emp ENABLE ROW MOVEMENT;

Для полного восстановления таблицы, ранее удалявшейся, команда FLASHBACK выглядит иначе. Вариант простого восстановления (из мусорной корзины):

FLASHBACK TABLE emp TO BEFORE DROP;

Вариант с переименованием (если с момента удаления таблица с именем EMP создавалась повторно):

FLASHBACK TABLE emp TO BEFORE DROP RENAME TO emp_old;

Если таблица переименовывалась несколько раз, вместо имени таблицы можно подставить внутреннее название для одной из старых версий таблицы, полученное из системной таблицы USER_RECYCLEBIN.

Технически такая возможность достигается сохранением старой структуры хранения ("сегмента") таблицы в табличном пространстве при выполнении DROP TABLE, что определяет границы применимости такого подхода.

Если же база данных работает в специальном режиме flashback, команда FLASHBACK позволяет восстановливать ее целиком:

FLASHBACK DATABASE TO TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;