Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6784 / 673 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 9:

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

< Лекция 8 || Лекция 9: 1234 || Лекция 10 >
Данные о номере последней транзакции, изменившей строку таблицы

В версии 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;
< Лекция 8 || Лекция 9: 1234 || Лекция 10 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Дмитрий Квашнёв
Дмитрий Квашнёв
Россия, Коломна, Московский государственный открытый университет, 2001
Павел Счетчиков
Павел Счетчиков
Россия, Казань