Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Январь 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : ретроспективные запросы и SCN


Информационный Канал Subscribe.Ru

Выпуск 25 (04.01.2003)

Ретроспективные запросы

Уважаемые подписчики! Этот выпуск посвящен различным вариантам использования пакета dbms_flashback (позволяющего выполнять ретроспективные запросы) и проблемам определения номера системного изменения (SCN) в версиях Oracle 8.1.7 и 9i. Продолжаем изучать наработки Тома Кайта. Прошу прощения за задержку с выходом этого выпуска - праздники все-таки и вообще - конец года, время переосмысления и подведения итогов... Кстати, вот здесь - некоторые итоги прошедшего 2002 года для проекта и его ведущего лично.

Поскольку выпуск, в каком-то смысле, юбилейный, хочу задать вам несколько вопросов:

  1. Нужны ли, по вашему мнению, выпуски, посвященные описанию формального синтаксиса операторов SQL?
  2. Интересует ли вас серия выпусков, посвященная использованию индексов различных типов?
  3. Нужны ли авторские выпуски вообще или можно ограничиться переводами?

Свои ответы вы можете отправить мне по электронной почте или опубликовать в гостевой книге проекта.

Пакет DBMS_FLASHBACK

Том,

  1. Ретроспективные запросы (flash back queries) нужны для разработчиков или для АБД?
  2. Я понял так, что для выполнения ретроспективного запроса необходимо указать значение SCN. Как разработчик может узнать значение SCN для транзакции?
  3. Одна транзакция может удалять данные несколько раз, и все эти удаления идентифицируются одним значением SCN. Что если я хочу посмотреть данные без учета одного конкретного удаления?

Не мог бы ты для объяснения привести пример ретроспективного запроса.

Ответ Тома Кайта

1) Для обоих. Это средство, оно может пригодиться и разработчикам, и АБД. Например, пользователь говорит: "Я случайно вечером удалил все свои данные". Если это разработчик, он может вернуться в прошлое, восстановить данные и решить эту проблему. Если пользователь - не большой специалист в базах данных, то вернуться в прошлое, восстановить данные и решить эту проблему сможет АБД. Разработчик может использовать эту возможность в приложениях: "Ладно, вот вам данные ДО вашего изменения, а вот - после". Тут пригодится возможность выполнять ретроспективные запросы.

2) Функция dbms_flashback.get_system_change_number возвращает текущее значение SCN в системе. Для поиска различных событий в прошлом с привязкой их к значению SCN можно также использовать Log Miner.

3) Необходимо вернуться в прошлое на системный SCN (а не SCN ваших транзакций), соответствующий требуемому моменту времени. Есть SCN системы и SCN транзакции. При выполнении ретроспективных запросов необходимо указывать SCN системы (SYSTEM SCN), а не SCN транзакций.

Теперь переходим к примеру...

Мы начнем с таблицы "сохраненных scn"

По ходу изменений мы будем запоминать значения scn в начале и в конце изменения. Эти значения можно будет использовать для возврата в прошлое на различные моменты времени.

tkyte@TKYTE9I.US.ORACLE.COM> create table keep_scn(msg varchar2(25), scn number); 

Table created.

Теперь мы будем многократно изменять таблицу EMP и фиксировать изменения в цикле. Мы запоминаем значение номера системного изменения до начала и после завершения. Так мы сможем вернуться по времени к началу, к середине или к концу изменений.

Помните - каждая фиксация транзакции в системе увеличивает значение SCN, - это наши "часы".

tkyte@TKYTE9I.US.ORACLE.COM> begin
  2      insert into keep_scn values ('start', dbms_flashback.get_system_change_number);
  3
  4      for i in 1 .. 100
  5      loop
  6          update SCOTT.EMP set sal = sal * 1.01;
  7          commit;
  8      end loop;
  9
 10      insert into keep_scn values ('stop', dbms_flashback.get_system_change_number);
 11      commit;
 12  end;
 13  / 
 
PL/SQL procedure successfully completed.

Теперь давайте начнем возвращаться в прошлое. Вернемся к началу:

tkyte@TKYTE9I.US.ORACLE.COM> variable x refcursor
tkyte@TKYTE9I.US.ORACLE.COM> set autoprint on
tkyte@TKYTE9I.US.ORACLE.COM> declare
  2      l_scn number;
  3  begin
  4      select scn into l_scn from keep_scn where msg = 'start';
  5      dbms_flashback.enable_at_system_change_number(l_scn);
  6      open :x for select ename, sal from SCOTT.EMP;
  7      dbms_flashback.disable;
  8  end;
  9  / 
  
PL/SQL procedure successfully completed. 

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5500
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300 

14 rows selected. 

tkyte@TKYTE9I.US.ORACLE.COM>

Теперь мы получим SCN, действовавший "в середине" изменения и посмотрим на данные в этот момент времени.

tkyte@TKYTE9I.US.ORACLE.COM> declare
  2      l_scn number;
  3  begin
  4      select trunc(avg(scn)) into l_scn from keep_scn;
  5      dbms_flashback.enable_at_system_change_number(l_scn);
  6      open :x for select ename, sal from SCOTT.EMP;
  7      dbms_flashback.disable;
  8  end;
  9  / 
  
PL/SQL procedure successfully completed. 

ENAME             SAL
---------- ----------
SMITH         1289.77
ALLEN         2579.56
WARD          2015.32
JONES         4796.37
MARTIN        2015.32
BLAKE         4594.84
CLARK         3949.95
SCOTT         4836.67
KING          8867.24
TURNER        2418.33
ADAMS         1773.46
JAMES         1531.58
FORD          4836.67
MILLER        2095.89 

14 rows selected. 

tkyte@TKYTE9I.US.ORACLE.COM>

Теперь получим SCN, действовавший в конце, и рассмотрим данные в этот момент: 

tkyte@TKYTE9I.US.ORACLE.COM> declare
  2      l_scn number;
  3  begin
  4      select scn into l_scn from keep_scn where msg = 'stop';
  5      dbms_flashback.enable_at_system_change_number(l_scn);
  6      open :x for select ename, sal from SCOTT.EMP;
  7      dbms_flashback.disable;
  8  end;
  9  / 
  
PL/SQL procedure successfully completed. 

ENAME             SAL
---------- ----------
SMITH         2163.82
ALLEN         4327.72
WARD          3381.09
JONES         8046.79
MARTIN        3381.09
BLAKE         7708.73
CLARK         6626.78
SCOTT         8114.43
KING         14876.49
TURNER        4057.24
ADAMS         2975.28
JAMES         2569.53
FORD          8114.43
MILLER        3516.27 

14 rows selected. 

tkyte@TKYTE9I.US.ORACLE.COM>

Наконец, мы вернемся к моменту начала изменений, откроем курсор, а затем используем его для восстановления данных по состоянию на этот момент времени. По сути, мы выполним откат изменений.

tkyte@TKYTE9I.US.ORACLE.COM> declare
  2      cursor emp_cur is select empno, sal from SCOTT.emp;
  3      l_rec emp_cur%rowtype;
  4      l_scn number;
  5  begin
  6      select scn into l_scn from keep_scn where msg = 'start';
  7      dbms_flashback.enable_at_system_change_number(l_scn);
  8      open emp_cur;
  9      dbms_flashback.disable;
 10      loop
 11          fetch emp_cur into l_rec;
 12          exit when emp_cur%notfound;
 13          update SCOTT.emp set sal = l_rec.sal where empno = l_rec.empno;
 14      end loop;
 15      close emp_cur;
 16      commit;
 17  end;
 18  / 

PL/SQL procedure successfully completed. 

tkyte@TKYTE9I.US.ORACLE.COM>

Убедимся, что сработало:

tkyte@TKYTE9I.US.ORACLE.COM> select ename, sal from SCOTT.emp; 

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5500
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300 

14 rows selected.

Комментарий от 27 января 2002 года

Том,

В 9.0.1 ямогу получить значение SCN системы динамически, с помощью dbms_flashback.get_system_change_number, например: 

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production 

SQL> select dbms_flashback.get_system_change_number from dual; 

GET_SYSTEM_CHANGE_NUMBER
------------------------
                   79414 
       
SQL> alter system switch logfile; 

System altered. 

SQL>  select dbms_flashback.get_system_change_number from dual; 

GET_SYSTEM_CHANGE_NUMBER
------------------------
                   79415

А как получить это значение SCN системы в предыдущих версиях сервера (8.1.7)?

Ответ Тома Кайта

Абсолютно не отраженный в документации и не поддерживаемый метод:

create global temporary table t (x number);
create or replace function get_scn return number
as
    pragma autonomous_transaction;
    l_scn number;
begin
    insert into t values (userenv('commitscn')) returning x into l_scn;
    commit;
    return l_scn;
end;
/
select get_scn from dual;

Поищите на сайте www.google.com в дискуссионных группах usenet слово commitscn для получения более детальной информации об этой замечательной функции.

Комментарий от 20 июля 2002 года

Я не могу повторить ваш пример. Получаю представленное ниже сообщение об ошибке. Что необходимо сделать для использования пакета dbms_flashback? Я использую Oracle 9i версии 1. Я предоставил право на выполнение dbms_flashback пользователю scott

  1  declare
  2          l_scn number;
  3      begin
  4          select scn into l_scn from keep_scn where msg = 'start';
  5          dbms_flashback.enable_at_system_change_number(l_scn);
  6          open :x for select ename, sal from SCOTT.EMP;
  7          dbms_flashback.disable;
  8*     end;
  9  /
declare
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
ORA-06512: at line 6

Ответ Тома Кайта

Похоже, вы только что создали таблицу scott.emp. Подождите минут 5.

А зачем ждать 5 минут?

Почему надо ждать, прежде чем выполнять Enable_At_time/scn?

Ответ Тома Кайта

В общем случае - не надо. Вы выполнили какой-то оператор ЯОД для таблицы scott.emp, вот о чем свидетельствует сообщение об ошибке. Вы пытаетесь вернуться в прошлое слишком далеко для этого объекта. Вы не представили весь сеанс sqlplus, как это делаю я, поэтому я не знаю точно, что вы делали, а что - нет.

Опять не работает...

Я попробовал еще раз. Не работает. На этот раз я не создавал никаких таблиц. Не выполнял никаких операторов ЯОД.

Я выполнил ALTER SYSTEM SET UNDO_RETENTION = 1800;

SQL> select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

Ответ Тома Кайта

Вы пытаетесь вернуться к моменту времени до создания таблицы.

Комментарий от 23 августа 2002 года

После создания функции get_scn, хотя никто, кроме меня, с сервером не работает, значение растет даже без фиксации транзакции. Растет не на 1, а на 5, 6 или 8. Почему? Так и было задумано?

Ответ Тома Кайта

Кто-то фиксирует транзакции. Посмотрите на очереди заданий и другие фоновые процессы.

Комментарий от 25 августа 2002 года

Никто ничего не фиксирует, кроме функции get_scn. Если фиксирует только она, то значение должно увеличиваться на 1... Может, значение SCN увеличивает один из фоновых процессов Oracle?

08:23:17 sysadm@BWYHRDEM SQL> select username from v$session where status='ACTIVE'
08:23:45   2  and username is not null; 

USERNAME
------------------------------
SYSADM Elapsed: 00:00:00.00

08:23:52 sysadm@BWYHRDEM SQL> show parameters job 

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
job_queue_interval                   integer 60
job_queue_processes                  integer 0

08:23:58 sysadm@BWYHRDEM SQL>
08:23:58 sysadm@BWYHRDEM SQL> select get_scn from  dual;    

   GET_SCN
----------
    852968 
 
Elapsed: 00:00:00.01

08:24:03 sysadm@BWYHRDEM SQL> /

   GET_SCN
----------
    852974 
 
Elapsed: 00:00:00.02

08:24:05 sysadm@BWYHRDEM SQL> /    

   GET_SCN
----------
    852980 
 
Elapsed: 00:00:00.01

Ответ Тома Кайта

Кто-то должен фиксировать. Не ограничивайтесь только активными, просмотрите все сеансы. Если подключены другие сеансы и они так или иначе зафиксируют транзакцию, значение SCN увеличится (блин, да просто завершение сеанса SQL*plus приведет к увеличению, поскольку там происходит фиксация при выходе).

А чего вообще беспокоиться? Значение SCN имеет тип number(38). Понадобится 3162315320785266140457397288 лет, чтобы исчерпать все возможные значения, даже если их генерируется сотни в секунду (допустим, при 1000/сек. их хватит навсегда).

Только один сеанс!

Меня не беспокоит исчерпание значений SCN... Но проблема в том, что, если к базе данных подключен я один, как может SCN увеличиваться более, чем на 1? Пожалуйста, попробуйте в своей системе.

14:41:39 sysadm@BWYHRDEM SQL> select username, status from v$session; 

USERNAME                       STATUS
------------------------------ --------
                               ACTIVE
                               ACTIVE
                               ACTIVE
                               ACTIVE
                               ACTIVE
                               ACTIVE
SYSADM                         ACTIVE 

7 rows selected. 

Elapsed: 00:00:00.02

14:41:47 sysadm@BWYHRDEM SQL> select get_scn from dual;    

   GET_SCN
----------
    852994 
 
Elapsed: 00:00:00.04

14:41:56 sysadm@BWYHRDEM SQL> /    

   GET_SCN
----------
    853000 
 
Elapsed: 00:00:00.02

14:41:57 sysadm@BWYHRDEM SQL> /

   GET_SCN
----------
    853006 
 
Elapsed: 00:00:00.01

14:41:58 sysadm@BWYHRDEM SQL> /

   GET_SCN
----------
    853012 

Elapsed: 00:00:00.02

Ответ Тома Кайта

Обратили внимание, что значение увеличивается на 6?

У меня увеличивается на два (как я и предполагал)

Не знаю, какой код вы выполняете, версию сервера, ОС и т.п.... Не могу ответить. Я бы лично на это не обращал внимания. Можете использовать Log Miner, чтобы разобраться, что происходит.

SCN увеличивается на 6

Да, вы правы... Увеличивается на 6... Почему?

ОС: SunOS 5.8
DB: 8.1.7.3

Теоретически, должно увеличиваться на 1... А увеличивается на 6.

appp02:BWYHRDEM:/homes/oracle> sqlplus 

SQL*Plus: Release 8.1.7.0.0 - Production on Sun Aug 25 21:06:21 2002 

(c) Copyright 2000 Oracle Corporation.  All rights reserved. 

Enter user-name: sysadm
Enter password: 

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production 

21:06:28 sysadm@BWYHRDEM SQL> drop table scn_t; 

Table dropped. 

Elapsed: 00:00:00.28

21:06:35 sysadm@BWYHRDEM SQL> create table scn_t(scn_n number, scn_d date); 

Table created. 

Elapsed: 00:00:00.03

21:06:54 sysadm@BWYHRDEM SQL> begin
21:07:00   2  for i in 1..10
21:07:13   3  loop
21:07:15   4  insert into scn_t select get_scn, sysdate from dual;
21:08:03   5  end loop;
21:08:07   6  end;
21:08:09   7  / 

PL/SQL procedure successfully completed. 

Elapsed: 00:00:00.12

21:08:11 sysadm@BWYHRDEM SQL> select scn_n, to_char(scn_d, 'hh:mi:ss') scn_time from scn_t;

     SCN_N SCN_TIME
---------- --------
    853737 09:08:10
    853743 09:08:10
    853749 09:08:10
    853755 09:08:11
    853761 09:08:11
    853767 09:08:11
    853773 09:08:11
    853779 09:08:11
    853785 09:08:11
    853791 09:08:11 

10 rows selected. 

Elapsed: 00:00:00.01

Хотя ограничение на SCN - очень большое... Просто интересно, почему увеличивается на 6?

Ответ Тома Кайта

Теоретически (и практически) должно увеличиваться на 2, но - вы используете версию 8173, которой у меня больше нет. Попробуйте на другой поддерживаемой версии, например, 8174 (я использовал ее).

По-прежнему не видел ВАШУ версию get_scn ;) Скопируйте и вставьте. Если бы я получал 10 центов каждый раз, когда кто-то копировал мой код (он говорил, что просто скопировал), а затем ИЗМЕНЯЛ его, - я был бы уже очень богатым.

Кстати, это мне теперь очень интересно,  - вы пытались использовать logminer??? Вы, похоже, очень любопытны, так вот, log miner может вам о многом рассказать...

8172:

ops$tkyte@ORA8I.WORLD> create global temporary table t (x number); 

Table created. 

ops$tkyte@ORA8I.WORLD> create or replace function get_scn return number
  2  as
  3      pragma autonomous_transaction;
  4      l_scn number;
  5  begin
  6      insert into t values (userenv('commitscn')) returning x into l_scn;
  7      commit;
  8      return l_scn;
  9  end;
 10  / 

Function created. 

ops$tkyte@ORA8I.WORLD> select get_scn from dual;

       GET_SCN
--------------
 6539836230307 

ops$tkyte@ORA8I.WORLD> select get_scn from dual; 

       GET_SCN
--------------
 6539836230309 

ops$tkyte@ORA8I.WORLD> select get_scn from dual; 

       GET_SCN
--------------
 6539836230311 

ops$tkyte@ORA8I.WORLD> select get_scn from dual; 

       GET_SCN
--------------
 6539836230313 

ops$tkyte@ORA8I.WORLD> select get_scn from dual; 

       GET_SCN
--------------
 6539836230315

8174:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create global temporary table t (x number); 

Table created. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function get_scn return number
  2  as
  3      pragma autonomous_transaction;
  4      l_scn number;
  5  begin
  6      insert into t values (userenv('commitscn')) returning x into l_scn;
  7      commit;
  8      return l_scn;
  9  end;
 10  / 

Function created. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> select get_scn from dual; 

           GET_SCN
------------------
     6532309038283 

ops$tkyte@ORA817DEV.US.ORACLE.COM> select get_scn from dual; 

           GET_SCN
------------------
     6532309038285 

ops$tkyte@ORA817DEV.US.ORACLE.COM> select get_scn from dual; 

           GET_SCN
------------------
     6532309038287

ops$tkyte@ORA817DEV.US.ORACLE.COM> select get_scn from dual; 

           GET_SCN
------------------
     6532309038289 

ops$tkyte@ORA817DEV.US.ORACLE.COM> select get_scn from dual; 

           GET_SCN
------------------
     6532309038291

SCN увеличивается на 2!

Я попытался использовать log miner... Теперь я вижу увеличение на два:

371720560 INSERT insert into "SYSADM"."SCN_T"("SCN_N","SCN_D") values
(371720559,TO_DATE('26-AUG-2002 14:19:38', 'DD-MON-YYYY HH24:MI:SS'));  

371720561 START set transaction read write;  

371720561 INSERT insert into "SYSADM"."T"("X") values (371720561);  

371720561 UPDATE update "SYSADM"."T" set "X" = 371720561 where ROWID =
'AAALIfAAjAAABRxAAQ';  

371720562 COMMIT commit;

Увеличение на 1 в начале и увеличение на 1 при фиксации.

Спасибо за быстрый ответ... Ты - лучший!

Комментарий читателя от 26 августа 2002 года

Привет, Том!

Я использую Oracle8i Enterprise Edition Release 8.1.7.0.0. При выполнении следующего блока кода:

  1  declare
  2          l_scn number;
  3      begin
  4          select scn into l_scn from keep_scn where msg  = 'start';
  5          dbms_flashback.enable_at_system_change_number(l_scn);
  6          open :x for select ename, sal from SCOTT.EMP;
  7          dbms_flashback.disable;
  8*     end;
  9  /
        dbms_flashback.enable_at_system_change_number(l_scn);
        *
ERROR at line 5:
ORA-06550: line 5, column 9:
PLS-00201: identifier 'DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER' must be
declared
ORA-06550: line 5, column 9:
PL/SQL: Statement ignored
ORA-06550: line 7, column 9:
PLS-00201: identifier 'DBMS_FLASHBACK.DISABLE' must be declared
ORA-06550: line 7, column 9:
PL/SQL: Statement ignored

получаю эти сообщения об ошибках.

Oracle 8.1.7.0 поддерживает пакет DBMS_FLASHBACK?. Я не нашел информации о нем в документации (Oracle Supplied Packages).

Ответ Тома Кайта

Это новое средство 9i (перечитайте первый комментарий, я ответил, как получить SCN в 8i, однако, ретроспективные запросы появились только в 9i).

Оригинал обсуждения этого вопроса можно найти здесь .

Copyright © 2002 Oracle Corporation

В следующем выпуске

Следующий выпуск будет целиком посвящен причинам и путям устранения ошибки ORA-1555. Выпуск выйдет на следующей неделе. Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное