GoldenLine
Zaloguj się
Adam Woźniak

Adam Woźniak software architect
and developer

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Witam

Mam zagwozdkę - może ktoś z Was zna odpowiedź.

Mamy tabelę:

TABELA
ID NUMBER
INFO VARCHAR2(10)

I robimy kilka updejtów tego samego wiersza w tabeli w ramach JEDNEJ transakcji:

rozpocznij transakcję

UPDATE TABELA SET info = 'ala' WHERE ID=1;
UPDATE TABELA SET info = 'ma' WHERE ID=1;
UPDATE TABELA SET info = 'kota' WHERE ID=1;

COMMIT;

Pytanie brzmi:
Jak zrobić, aby w Oracle została zapisana (w tabeli, kolejce, nie istotne) informacja jedynie o ostatnim UPDATE?

To, czego bym oczekiwał to rekord w rodzaju:
ID: 1
INFO: kota
UPDATE_TIME: 2010.01.11 19:30:44

Naturalnie szukam jakiegoś wydajnego sposobu na osiągnięcie tego działania.

Będę wdzięczny za pomoc / wskazówki.

Pozdrawiam, AdamAdam Woźniak edytował(a) ten post dnia 11.01.10 o godzinie 19:31
Link do wypowiedziLink
Paweł W.

Paweł W. Oracle & PL/SQL
Developer

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Wydaje mi się że bez żadnych dodatkowych zabiegów uzyskasz efekt który opisujesz...
Nie jest tak?
Link do wypowiedziLink
Bartosz Ślepowronski

Bartosz Ślepowronski Problem? Jaki
problem?

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

[ciach ciach]
Chcialem dokladnie to co Pawel napisac.
Link do wypowiedziLink
Grzegorz D.

Grzegorz D. PL/SQL Developer

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Nie wiem czy dobrze rozumiem (pomijam to ,że pierwszą myślą było to o czym napisał Paweł), ale przy update możesz zapisywać do jakiejś zmiennej id wiersza, na którym jest update. Po commit wyciągasz tą zmienną i masz ten wiersz.
Link do wypowiedziLink
Adam Woźniak

Adam Woźniak software architect
and developer

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Napiszę precyzyjniej:

Po wykonaniu takich 3 transakcji (zakończonych COMMIT-em), chcę w bazie mieć 3 rekordy, gdzie w każdym rekordzie będzie ID modyfikowanego rekordu i informacje o ostatniej (zaCOMMITowanej) wartości kolumny INFO.

Oczywiście chcę tę funkcjonalność osiągnąć na poziomie bazy danych - nie mam dostępu do aplikacji robiących te UPDATE-y.

Pozdrawiam, Adam
Link do wypowiedziLink
Adam Woźniak

Adam Woźniak software architect
and developer

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Grzegorz Drzymała:
Nie wiem czy dobrze rozumiem (pomijam to ,że pierwszą myślą było to o czym napisał Paweł), ale przy update możesz zapisywać do jakiejś zmiennej id wiersza, na którym jest update. Po commit wyciągasz tą zmienną i masz ten wiersz.

Tego też nie napisałem:
W ramach takich transakcji, może być modyfikowanych wiele rekordów tabeli TABELA. Innymi słowy, po COMMIT-cie chcę mieć w bazie utrwaloną informację, które rekordy zostały zaktualizowane i jaką (ostateczną) wartość miała kolumna INFO.

Może powiem dokładniej o to, co chce osiągnąć:
Muszę monitorować zmiany w tabeli TABELA.
Pierwszym pomysłem, jest dodanie trigger-a ON UPDATE, który to trigger do jakiejś kolejki AQ wstawia rekord {ID, wartość kolumny INFO}. Tyle, że to rozwiązanie ma tę wadę, że w ramach jednej transakcji, jeśli zadany wiersz z tabeli TABELA będzie aktualizowany 10 razy, to do kolejki AQ zostanie dodanych 10 rekordów.
Chciałbym (życzyłbym sobie), aby był to tylko 1 rekord.

Pozdrawiam, AdamAdam Woźniak edytował(a) ten post dnia 11.01.10 o godzinie 18:31
Link do wypowiedziLink
Grzegorz D.

Grzegorz D. PL/SQL Developer

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Adam Woźniak:
Grzegorz Drzymała:
Nie wiem czy dobrze rozumiem (pomijam to ,że pierwszą myślą było to o czym napisał Paweł), ale przy update możesz zapisywać do jakiejś zmiennej id wiersza, na którym jest update. Po commit wyciągasz tą zmienną i masz ten wiersz.

Tego też nie napisałem:
W ramach takich transakcji, może być modyfikowanych wiele rekordów tabeli TABELA. Innymi słowy, po COMMIT-cie chcę mieć w bazie utrwaloną informację, które rekordy zostały zaktualizowane i jaką (ostateczną) wartość miała kolumna INFO.

Może powiem dokładniej o to, co chce osiągnąć:
Muszę monitorować zmiany w tabeli TABELA.
Pierwszym pomysłem, jest dodanie trigger-a ON UPDATE, który to trigger do jakiejś kolejki AQ wstawia rekord {ID, wartość kolumny INFO}. Tyle, że to rozwiązanie ma tę wadę, że w ramach jednej transakcji, jeśli zadany wiersz z kolumny TABELA będzie aktualizowany 10 razy, to do kolejki AQ zostanie dodanych 10 rekordów.
Chciałbym (życzyłbym sobie), aby był to tylko 1 rekord.

Pozdrawiam, Adam

A nie można użyć MERGE w triggerze? Tak żeby wstawił ci pierwszą wartość, a później, gdy dany wiersz (z danym id) zmieni się, zrobić update?
Link do wypowiedziLink

konto usunięte

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Zastanawiałem się nad problemem.
Trigger per each row, albo statement nie przyda sie, merge także. Więc może
funkcjonalność oracla:
DBMS_CHANGE_NOTIFICATION i dodatkowo flashback rekordów.

http://www.oracle-base.com/articles/10g/dbms_change_no...

Te rozwiązanie działało by asynchronicznie i nie obciążałoby transakcji. Rozwiązanie nie jest proste (wręcz karkołomne), lecz przyjemne.

Czekam na rozwiązania kolegów i propozycje.Robert Zapadka edytował(a) ten post dnia 11.01.10 o godzinie 18:37
Link do wypowiedziLink
Adam Woźniak

Adam Woźniak software architect
and developer

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Grzegorz Drzymała:
A nie można użyć MERGE w triggerze? Tak żeby wstawił ci pierwszą wartość, a później, gdy dany wiersz (z danym id) zmieni się, zrobić update?

Hmmm...
Jeśli miałbym użyć tutaj MERGE, to rozumiem, że MERGE ten musiałby operować na tabeli z 3 wierszami:
TRANSACTION_ID - unikalny identyfikator transakcji Oraclowej, aby
rozróżniać aktualizacje per każda z transakcji

ID - ID rekordu z tabeli TABELA
INFO - nowa wartość kolumny TABELA.INFO

Dobrze rozumiem?

Poza tym, obawiam się, że takie rozwiązanie może być dla nas nieakceptowalne ze względu na wydajność, gdyż, jak zgaduję, MERGE przez Oracle realizowany jest jako SELECT (aby sprawdzić czy rekord taki już jest), a następnie INSERT lub UPDATE.
Coś się obawiam, że ten wspomniany SELECT może być dosyć ciężki.

Poza tym nawet nie wiem, czy istnieje coś takiego jak unikalny identyfikator transakcji ;]

Pozdrawiam, Adam
Link do wypowiedziLink

konto usunięte

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Można pobrać UID transakcji:
DBMS_Transaction.LOCAL_TRANSACTION_ID:

CREATE OR REPLACE FUNCTION get_trans_for_table RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
x VARCHAR2(20);
BEGIN
INSERT INTO t
(testcol)
VALUES
(2);

x := dbms_transaction.local_transaction_id;
COMMIT;

RETURN x;
END atf;
/
Link do wypowiedziLink
Dawid Glowacki

Dawid Glowacki BI/DW/ETL Consultant

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Adam Woźniak:
Grzegorz Drzymała:
Nie wiem czy dobrze rozumiem (pomijam to ,że pierwszą myślą było to o czym napisał Paweł), ale przy update możesz zapisywać do jakiejś zmiennej id wiersza, na którym jest update. Po commit wyciągasz tą zmienną i masz ten wiersz.

Tego też nie napisałem:
W ramach takich transakcji, może być modyfikowanych wiele rekordów tabeli TABELA. Innymi słowy, po COMMIT-cie chcę mieć w bazie utrwaloną informację, które rekordy zostały zaktualizowane i jaką (ostateczną) wartość miała kolumna INFO.

Może powiem dokładniej o to, co chce osiągnąć:
Muszę monitorować zmiany w tabeli TABELA.
Pierwszym pomysłem, jest dodanie trigger-a ON UPDATE, który to trigger do jakiejś kolejki AQ wstawia rekord {ID, wartość kolumny INFO}. Tyle, że to rozwiązanie ma tę wadę, że w ramach jednej transakcji, jeśli zadany wiersz z tabeli TABELA będzie aktualizowany 10 razy, to do kolejki AQ zostanie dodanych 10 rekordów.
Chciałbym (życzyłbym sobie), aby był to tylko 1 rekord.

Pozdrawiam, AdamAdam Woźniak edytował(a) ten post dnia 11.01.10 o godzinie 18:31

Jeżeli robisz to dla sportu/zabawy i nie dbasz o to jak długo będzie przechowywany taki audyt to użyj flashback.

Jeżeli ma to być profesjonalne/długotrwałe rozwiązanie to najlepszą opcją będzie trigger i dodatkowa tabela z wartościami audytu (trigger 'after insert/update/delete' for each row, dobrze jest mieć sekwencje dla tabeli audytu).

Nie za bardzo rozumiem dlaczego chcesz eliminować duplikatu w tabeli audyt: przy dobrym design wiersze których wartości nie ulegają zmianie nie powinny być przedmiotem operacji UPDATE.

Pozdrawiam
Link do wypowiedziLink
Adam Woźniak

Adam Woźniak software architect
and developer

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Dawid Glowacki:
Jeżeli robisz to dla sportu/zabawy i nie dbasz o to jak długo będzie przechowywany taki audyt to użyj flashback.

To nie zabawa. To produkcja.
Flashback? Nie mogę wspierać się flashback, bo nie jestem administratorem tej bazy i tym samym nie mam żadnych gwarancji co do tego jak flashback będzie skonfigurowany na tejże bazie.
Jeżeli ma to być profesjonalne/długotrwałe rozwiązanie to najlepszą opcją będzie trigger i dodatkowa tabela z wartościami audytu (trigger 'after insert/update/delete' for each row,

Yyyy... Ale w ten sposób nie osiągnę poszukiwanego przeze mnie zachowania: chcę mieć informację jedynie o ostatniej (zaCOMMITowanej) wartości kolumny INFO.
dobrze jest mieć sekwencje dla tabeli audytu).

Masz na myśli sekwencję, z której będę generował primary key dla tabeli z danymi audytowymi?
Nie za bardzo rozumiem dlaczego chcesz eliminować duplikatu w tabeli audyt: przy dobrym design wiersze których wartości nie ulegają zmianie nie powinny być przedmiotem operacji UPDATE.

Primo:
Nie jestem zainteresowany zmianami kolumny INFO w ramach działającej transakcji. Interesuje mnie jedynie nowa wartość kolumny INFO, która została utrwalona w bazie przez COMMIT.

Secundo:
[..] przy dobrym design wiersze których wartości nie ulegają zmianie nie powinny być przedmiotem operacji UPDATE.

Dobrym designie czego? Systemów, które wykonują operację UPDATE na tabeli TABELA? Nie mam żadnego wpływu na te systemy.

Pozdrowienia, AdamAdam Woźniak edytował(a) ten post dnia 11.01.10 o godzinie 19:43
Link do wypowiedziLink
Dawid Glowacki

Dawid Glowacki BI/DW/ETL Consultant

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Adam Woźniak:
Primo:
Nie jestem zainteresowany zmianami kolumny INFO w ramach działającej transakcji. Interesuje mnie jedynie nowa wartość kolumny INFO, która została utrwalona w bazie przez COMMIT.

Hmm ciekawe wymagania...

Jeżeli naprawdę wiesz co chcesz zrobić (?? :)) no to najłatwiej będzie przed każdym commit wywoływać jakąś procedure, która zrzuci wartość do tabeli audyt...

Gdy to nie wchodzi w grę no to się chyba poddaje...

Pozdrawiam
Link do wypowiedziLink
Dawid Glowacki

Dawid Glowacki BI/DW/ETL Consultant

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Ewentualnie widok zmaterializowany z odświerzaniem w trybie FAST po każdym commit, to będzie wymagało oczywiście logu materialized view na tabeli bazowej...
Link do wypowiedziLink
Adam Woźniak

Adam Woźniak software architect
and developer

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Dawid Glowacki:
Adam Woźniak:
Primo:
Nie jestem zainteresowany zmianami kolumny INFO w ramach działającej transakcji. Interesuje mnie jedynie nowa wartość kolumny INFO, która została utrwalona w bazie przez COMMIT.

Hmm ciekawe wymagania...

Hmmm... Nie wiem, co tak Ciebie w tym dziwi? ;]
Powtrzam: jeśli w ramach JEDNEJ transakcji pewien wiersz z tabeli TABELA (konkretnie: kolumna INFO) zmieniana jest 10 razy, to mnie JEDYNIE interesuje ta OSTATNIA wartość kolumny INFO - wartość, która w końcu została utrwalona w bazie danych przez wywołanie COMMIT.
To, że w tejże transakcji kolumna INFO tego rekordu miała inne (tymczasowe) wartości mnie kompletnie nie interesuje.
Jeżeli naprawdę wiesz co chcesz zrobić (?? :)) no to najłatwiej będzie przed każdym commit wywoływać jakąś procedure, która zrzuci wartość do tabeli audyt...

?
Ale skąd, przed COMMIT-em, mam czerpać wiedzę o tym, które rekordy z tabeli TABELA były zmienione przez operacje UPDATE na tejże tabeli?
Te informacje są trzymane w jakieś "pomocniczej" strukturze danych?
Gdy to nie wchodzi w grę no to się chyba poddaje...

:)
W każdym razie i Tobie i innym pomagającym - dziękuję!

Pozdrawiam, Adam
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Jeżeli ma to być profesjonalne/długotrwałe rozwiązanie to najlepszą opcją będzie trigger i dodatkowa tabela z wartościami audytu (trigger 'after insert/update/delete' for each row,

Yyyy... Ale w ten sposób nie osiągnę poszukiwanego przeze mnie zachowania: chcę mieć informację jedynie o ostatniej (zaCOMMITowanej) wartości kolumny INFO.

Nie jestem pewien czy dobrze zrozumiałem problem, ale mając tabelę test(id number, a number) i tabelę test_audyt(id number, a number, zmiana date), to żeby w tabeli test_audyt utrwalić tylko ostatnią zmianę mogę zrobić np. coś takiego:

create or replace trigger trg_test
before update on test for each row
declare
v_dummy test_audyt.id%type;
begin
select id into v_dummy
from test_audyt
where id=:new.id;
update test_audyt
set a=:new.a,
zmiana=sysdate
where id=:new.id;
exception
when no_data_found then
insert into test_audyt(id, a, zmiana)
values (:new.id, :new.a, sysdate);
end;
Link do wypowiedziLink
Dawid Glowacki

Dawid Glowacki BI/DW/ETL Consultant

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Adam Woźniak:
Hmmm... Nie wiem, co tak Ciebie w tym dziwi? ;]

No cóż, po prostu z takim podejściem się jeszcze nie spotkałem. Ale cieszę się że jeszcze są rozwiązania które mogą mnie zadziwić:)

Tak napisałem powyżej: jeżeli nawet widok zmaterializowany się nie
kwalifikuje (refresh FAST on commit) no to znowu będę musiał powiedzieć że się poddaje:)

PozdrawiamDawid Glowacki edytował(a) ten post dnia 11.01.10 o godzinie 20:19
Link do wypowiedziLink
Sebastian Kolski

Sebastian Kolski programista/DBA

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Najprostszym rozwiązaniem jest Oracle Total Recall czyli flashback data archive. Opcja a pozwala śledzić wszystkie zmiany transakcyjne w tabeli przez cały czas jej życia.
Przykład użycia w celach audytu.
Tyle, że do włączenia tego rozwiązania potrzeba określonych przywilejów. Jedną z zalet jest to, że danych archiwalnych nie jest w stanie nikt zmienić, co w przypadku własnych table audytowych nie jest zagwarantowane.
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Nie jestem pewien czy dobrze zrozumiałem problem (....)

Faktycznie nie zrozumiałem. Ale powinno zadziałać co innego:

1) create global temporary table temp_trnas_id (tr_id number) on commit delete rows;
2) create sequence seq_update start with 1 increment by 1 nocache;
3) tabelki tak jak opisane w mojej przedniej wiadomości (czyli test i test_audyt)
4) trigger:

create or replace
trigger trg_test
before update on test for each row
declare
v_dummy test_audyt.id%type;
v_tr_id number;
begin

begin

select tr_id into v_tr_id
from temp_trnas_id;


exception when no_data_found then
begin
select seq_update.nextval into v_tr_id
from dual;

insert into temp_trnas_id values (v_tr_id);
end;

end;

select id into v_dummy
from test_audyt
where id=:new.id
and tr_id= v_tr_id;

update test_audyt
set a=:new.a,
zmiana=sysdate
where id=:new.id
and tr_id= v_tr_id;

exception
when no_data_found then
insert into test_audyt(id, a, zmiana, tr_id)
values (:new.id, :new.a, sysdate, v_tr_id);
end;

Czy to wchodzi w grę?Kamil Stawiarski edytował(a) ten post dnia 11.01.10 o godzinie 21:11
Link do wypowiedziLink

konto usunięte

Temat: [Oracle] Czy da się utrwalić informacje o ostatnim UPDATE?

Sebastian Kolski:
Najprostszym rozwiązaniem jest Oracle Total Recall czyli flashback data archive. Opcja a pozwala śledzić wszystkie zmiany transakcyjne w tabeli przez cały czas jej życia.
Przykład użycia w celach audytu.
Tyle, że do włączenia tego rozwiązania potrzeba określonych przywilejów. Jedną z zalet jest to, że danych archiwalnych nie jest w stanie nikt zmienić, co w przypadku własnych table audytowych nie jest zagwarantowane.

Proponowałem to rozwiązanie. Interesująco się zachowuje w połączeniu z DBMS_CHANGE_NOTIFICATION i wykrywaniem zmian na tabelach. Triggery na tabelach produktu firm trzecich nie daje możliwości wykryć zmian globalnych na rekordzie.
Kamil Stawiarski:
Nie jestem pewien czy dobrze zrozumiałem problem (....)

Faktycznie nie zrozumiałem. Ale powinno zadziałać co innego:

1) create global temporary table temp_trnas_id (tr_id number) on commit delete rows;[/edited]

globalna tabela jest czyszczona po zakonczeniu sessji bazodanowej oracle'a. Wiec nie wiem jaki mialby być wpływ tego typu tabeli. Moim zdaniem należy rozpatrywać sposób monitoringu produktu firm trzecich, a nie aplikacji tworzonej. Materialized View wydaje się też nie optymalnym rozwiązaniem, gdyż ciezko będzie się modyfikowało w niej dane (co jest wymogiem). Co do triggerów są mało skalowalne. Załóżmy mamy 100 tabel do monitoringu więc 100 triggerów ( oczywiscie można zaimplementować logikę w package, lecz nie jest to wyjście, gdyż komplikują się procesy utrzymania nowo utworzonego produktu)

Reasumując są 3 wyjścia:
+ Flashback,
+ DBMS_CHANGE_NOTIFICATION,
+ triggerRobert Zapadka edytował(a) ten post dnia 11.01.10 o godzinie 21:35
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Oracle mass UPDATE




Wyślij zaproszenie do
Anuluj