Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Отслеживание изменений в таблице  [new]
volfvolf
Member

Откуда: Киев
Сообщений: 7
Уважаемые форумчане помогите победить следующую задачку.

Oracle 10 (win\linux)
В БД есть некоторое количество таблиц (порядка 10-20 шт.). В каждой таблице есть поле ИДшника (правда название этого поля в каждой таблице своё). Необходимо узнать (для каждой таблицы) какие записи были изменены. Фактически нужно получать список ИДшников записей и один из статусов
1 - запись добавлена\изменена или
2 - запись была удалена.
Получать такую информацию необходимо с какой-то периодичностью. т.е. изначально все записи считаются новыми, а потом (скажем через каждые полмесяца) нужно узнавать какие изменились\удалились. Очень похоже на репликацию. Но данная процедура инициируется по требованию.

Задача осложняется тем, что вносить изменения в структуру таблиц нельзя и навешивать триггеры - тоже.
В некоторые таблицы (в определённое время суток) происходит очень активная запись (Insert, Update). Поэтому отклик от системы желательно не должен заметно ухудшатся.

Как быть ?
17 июл 08, 23:23    [5950991]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
Alexander Ryndin
Member

Откуда:
Сообщений: 4917
Блог
volfvolf
Уважаемые форумчане помогите победить следующую задачку.

Oracle 10 (win\linux)
В БД есть некоторое количество таблиц (порядка 10-20 шт.). В каждой таблице есть поле ИДшника (правда название этого поля в каждой таблице своё). Необходимо узнать (для каждой таблицы) какие записи были изменены. Фактически нужно получать список ИДшников записей и один из статусов
1 - запись добавлена\изменена или
2 - запись была удалена.
Получать такую информацию необходимо с какой-то периодичностью. т.е. изначально все записи считаются новыми, а потом (скажем через каждые полмесяца) нужно узнавать какие изменились\удалились. Очень похоже на репликацию. Но данная процедура инициируется по требованию.

Задача осложняется тем, что вносить изменения в структуру таблиц нельзя и навешивать триггеры - тоже.
В некоторые таблицы (в определённое время суток) происходит очень активная запись (Insert, Update). Поэтому отклик от системы желательно не должен заметно ухудшатся.

Как быть ?


Копай в сторону LogMiner http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/logminer.htm#SUTIL019
18 июл 08, 00:56    [5951216]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
Elic
Member

Откуда:
Сообщений: 29990
volfvolf
1 - запись добавлена
insert into t_replicated
  select t.*, 'inserted', sysdate from t
    where id not in (select id from t_replicated);
volfvolf
изменена или
merge into t_replicated t1
  using
  ( select * from
    ( select * from t
      minus
      select id, col1, col2, ... /* кроме status&changed */ from t_replicated
    )
    where id in (select id from t_replicated)
  ) t2
  on (t1.id = t2.id)
  when matched then update set col1 = t2.col1, col2 = t2.col2, ..., status = 'updated', changed = sysdate
  /*9i*/when not matched then insert (id) values (null)
;
volfvolf
2 - запись была удалена.
update t_replicated set status = 'deleted', changed = sysdate
  where id not in (select id from t)
    and status <> 'deleted';
18 июл 08, 11:19    [5952623]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
volfvolf
Member

Откуда: Киев
Сообщений: 7
2 Alexander Ryndin
Спасибо. Но помоему это средство не совсем подходит, т.к. возвращает изменения в виде SQL скрипта. Конечно можно этот скрипт пропарсить .... но это излишние навороты (как мне кажется :) ). Или LogMiner может возвращать значение необходимого поля (для изменившейся записи)?
2 Elic
Спасибо. Да, сам подумывал о таком варианте, на крайний случай. :) Проблема такого подхода - это время выполнения :(

Может есть еще какие-нибудь средства?
18 июл 08, 18:20    [5956855]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
Leonid Gurevich
Member

Откуда:
Сообщений: 2223
Появилась такая идея. Для каждой таблицы создать MVIEW LOG, но без самих MVIEWs. Затем смотреть изменения в скрытых таблицах-логах с именами MLOG$_<table_name>. Время от времени чистить эти логи. Правда, при этом Oracle для заполнения логов создает невидимые триггеры на таблицах. Так что сначала нужно проконсультироваться с разработчиками вашего софта.
18 июл 08, 18:42    [5956972]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
Alexander Ryndin
Member

Откуда:
Сообщений: 4917
Блог
volfvolf
2 Alexander Ryndin
Спасибо. Но помоему это средство не совсем подходит, т.к. возвращает изменения в виде SQL скрипта. Конечно можно этот скрипт пропарсить .... но это излишние навороты (как мне кажется :) ). Или LogMiner может возвращать значение необходимого поля (для изменившейся записи)?
2 Elic
Спасибо. Да, сам подумывал о таком варианте, на крайний случай. :) Проблема такого подхода - это время выполнения :(

Может есть еще какие-нибудь средства?


Парсинг реализуется несложно. Хотя все равно ручками придется постучать по клавиатуре,
Есть еще одно средство. Flashback запросы, но там тоже не все просто. В частности - время undo нужно выверять будет
18 июл 08, 23:43    [5957809]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7432
Посмотрите пакет dbms_wm... Узнаете много интересного...
SQL> create table A(id number primary key, fldA char(1), fldB char(1))
  2  /
Table created.
SQL> call dbms_wm.enableversioning('A', 'VIEW_WO_OVERWRITE')
  2  /
Call completed.
SQL> insert into a values(1, 'A', 'B')
  2  /
1 row created.
SQL> update a set fldA = 'Q'
  2  /
1 row updated.
SQL> delete from a
  2  /
1 row deleted.
SQL> commit
  2  /
Commit complete.
SQL> select id, flda, fldb,
  2  substr(WM_USERNAME, 1, 8) USERNAME,
  3  WM_OPTYPE, substr(WM_CREATETIME, 1, 20) WM_CREATETIME,
  4  substr(WM_RETIRETIME, 1, 20) WM_RETIRETIME from a_hist
  5  /
        ID F F USERNAME W WM_CREATETIME        WM_RETIRETIME                                 
---------- - - -------- - -------------------- --------------------                          
         1 A B HMS      I 20-JUN-06 10.19.14.8 20-JUN-06 10.19.14.9                          
         1 Q B HMS      U 20-JUN-06 10.19.14.9 20-JUN-06 10.19.15.1                          
         1 Q B HMS      D 20-JUN-06 10.19.15.1                                               
SQL> call DBMS_WM.BeginDDL('A')
  2  /
Call completed.
SQL> ALTER TABLE a_lts ADD (comments VARCHAR2(5))
  2  /
Table altered.
SQL> call DBMS_WM.CommitDDL('A')
  2  /
Call completed.
SQL> insert into a values(1, 'A', 'B', 'Comm.')
  2  /
1 row created.
SQL> commit
  2  /
Commit complete.
SQL> select id, flda, fldb, Comments,
  2  substr(WM_USERNAME, 1, 8) USERNAME, WM_OPTYPE,
  3  substr(WM_CREATETIME, 1, 20) WM_CREATETIME,
  4  substr(WM_RETIRETIME, 1, 20) WM_RETIRETIME from a_hist
  5  /
        ID F F COMME USERNAME W WM_CREATETIME        WM_RETIRETIME                           
---------- - - ----- -------- - -------------------- --------------------                    
         1 A B       HMS      I 20-JUN-06 10.19.14.8 20-JUN-06 10.19.14.9                    
         1 Q B       HMS      U 20-JUN-06 10.19.14.9 20-JUN-06 10.19.15.1                    
         1 Q B       HMS      D 20-JUN-06 10.19.15.1 20-JUN-06 10.19.18.5                    
         1 A B Comm. HMS      U 20-JUN-06 10.19.18.5                                         
SQL> call dbms_wm.disableversioning('A')
  2  /
Call completed.
SQL> drop table a
  2  /
Table dropped.
SQL> 
18 июл 08, 23:58    [5957857]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
volfvolf
Member

Откуда: Киев
Сообщений: 7
2 Relic Hunter
Спасибо. Очень интересный вариант. Он мне больше всего подходит.
А что кто скажет по поводу Oracle Steams?
20 июл 08, 22:17    [5961070]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
Elic
Member

Откуда:
Сообщений: 29990
volfvolf
Relic Hunter
SQL> call dbms_wm.enableversioning('A', 'VIEW_WO_OVERWRITE')
Спасибо. Очень интересный вариант. Он мне больше всего подходит.
SQL> select object_type, object_name from user_objects where object_name like 'A' or object_name like 'A\_%' escape '\' order by 1,2;

OBJECT_TYPE         OBJECT_NAME
------------------- ------------------------------
INDEX               A_AP1$
INDEX               A_AP2$
INDEX               A_PKI$
INDEX               A_TI$
TABLE               A_AUX
TABLE               A_LT
VIEW                A
VIEW                A_BASE
VIEW                A_BPKC
VIEW                A_CONF
VIEW                A_CONS
VIEW                A_DIFF
VIEW                A_HIST
VIEW                A_LOCK
VIEW                A_MW
VIEW                A_PKC
VIEW                A_PKD
VIEW                A_PKDB
VIEW                A_PKDC

19 rows selected.

SQL> select TEXT from user_views where view_name='A';

TEXT
--------------------------------------------------------------------------------
SELECT ID,FLDA,FLDB
     FROM OWNER_SOU.A_base b

SQL> select TEXT from user_views where view_name='A_BASE';

TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT ID,FLDA,FLDB, rowid WM_rowid, version WM_version, nextver WM_nextver, delstatus WM_delstatus, ltlock WM_ltlock,createTime WM_createtime, retireTime WM_retiretime
      FROM OWNER_SOU.A_LT LT
      WHERE delstatus >=0 and
       ( ((sys_context('lt_ctx','TsInstant') is null) and
          ((nvl(sys_context('lt_ctx', 'state'),'LIVE') = 'LIVE' and
            nvl(sys_context('lt_ctx','version'),-1) = -1 and
            retiretime is null and
            version in (select parent_vers from wmsys.wm$table_versions_in_live_view
                        where table_name = 'OWNER_SOU.A'))
          OR
          ((nvl(sys_context('lt_ctx', 'state'), 'LIVE') != 'LIVE' or nvl(sys_context('lt_ctx','version'),-1) != -1) and
          ((version = 0 and nextver = '-1') or
           (version in (select parent_vers from wmsys.wm$table_parvers_view
                        where table_name = 'OWNER_SOU.A') and
           (nextver = '-1' or
           (not exists (select 1 from wmsys.wm$current_nextvers_view where nextver = next_vers) and nextver != ',0,')))))))
       OR
         ((sys_context('lt_ctx','TsInstant') is not null ) and
         ((nvl(SYS_CONTEXT('lt_ctx','state'),'LIVE') = 'LIVE' and
          (LT.createTime <= decode(sys_context('lt_ctx','TsInstant'), null, null, to_timestamp_tz(sys_context('lt_ctx','TsInstant'), 'mmddyyyyhh24missfftzh:tzm', 'NLS_DATE_LANGUAGE=AMERICAN')) and
          (LT.retireTime is null or LT.retireTime > decode(sys_context('lt_ctx','TsInstant'), null, null, to_timestamp_tz(sys_context('lt_ctx','TsInstant'), 'mmddyyyyhh24missfftzh:tzm', 'NLS_DATE_LANGUAGE=AMERICAN')) )) and
           LT.version in (select parent_vers from wmsys.wm$table_versions_in_live_view
                                           where table_name = 'OWNER_SOU.A'))
         OR
         ((nvl(SYS_CONTEXT('lt_ctx','state'),'LIVE') != 'LIVE') and
         ((LT.version in (select parent_vers from wmsys.wm$table_ws_parvers_view

SQL> select TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT from user_triggers where TABLE_NAME = 'A_BASE';

TRIGGER_NAME                   TRIGGER_TYPE     TRIGGERING_EVENT
------------------------------ ---------------- --------------------
OVM_INSERT_1                   INSTEAD OF       INSERT
OVM_UPDATE_1                   INSTEAD OF       UPDATE
OVM_DELETE_1                   INSTEAD OF       DELETE

SQL> desc A_LT
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 FLDA                                               CHAR(1)
 FLDB                                               CHAR(1)
 VERSION                                            NUMBER(38)
 CREATETIME                                         TIMESTAMP(6) WITH TIME ZONE
 RETIRETIME                                         TIMESTAMP(6) WITH TIME ZONE
 NEXTVER                                            VARCHAR2(500)
 DELSTATUS                                          NUMBER(38)
 LTLOCK                                             VARCHAR2(100)
volfvolf
вносить изменения в структуру таблиц нельзя и навешивать триггеры - тоже.
21 июл 08, 09:45    [5961615]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
volfvolf
Member

Откуда: Киев
Сообщений: 7
2 Elic
Вот блин...как всегда все оказалось не все так просто :)
Спасибо . Что-то у меня складывается такое впечатление, что все эти разные пакеты просто убирают некоторую рутину. (К сожалению с Ораклом я знаком совсем недавно) И все реализации крутятся вокруг стандартных механизмов (триггеры, вьюшки ....) :(
Неужели нету ничего что основано на каких-то внутренних механизмах?
21 июл 08, 14:27    [5963671]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
Elic
Member

Откуда:
Сообщений: 29990
volfvolf
Неужели нету ничего что основано на каких-то внутренних механизмах?
Есть ORA_ROWSCN+ROWDEPENDENCIES. Но это, наверняка, потребует пересоздания таблиц.
21 июл 08, 15:25    [5964238]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
spotted
Member

Откуда: Киев
Сообщений: 89
Не особо понятна постановка задачи. Одно дело - смотреть изменения между любыми двуми произвольными точками времени, другое - время от времени смотреть, что изменилось, и после этого начинать всё сначала.
21 июл 08, 18:49    [5965680]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
volfvolf
Member

Откуда: Киев
Сообщений: 7
spotted
Не особо понятна постановка задачи. Одно дело - смотреть изменения между любыми двуми произвольными точками времени, другое - время от времени смотреть, что изменилось, и после этого начинать всё сначала.

...Фактически нужно получать список ИДшников записей и один из статусов...
с момента последнего такого просмотра по текущее время. т.е. как бы "пакет последних изменений", который можно потом как-то обработать.
21 июл 08, 18:57    [5965698]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
serger75
Member

Откуда:
Сообщений: 1
Конечно Oracle Streams
21 июл 08, 21:10    [5965993]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблице  [new]
volfvolf
Member

Откуда: Киев
Сообщений: 7
serger75
Конечно Oracle Streams

Пожалуйста приведите пример.
22 июл 08, 11:22    [5967635]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить