Blog > Komentarze do wpisu
Automatyczne śledzenie zmian struktury bazy danych
Jeśli chcesz poznać prosty mechanizm automatycznego śledzenia zmian struktury bazy danych oparty o triggery, który może być przydatny do przykładowo synchronizowania dwóch instancji bazy danych Oracle to zapraszam do przeczytania poniższego wpisu.

 

 

>>> ZACZYNAMY <<

 

 

Wyobraźmy sobie, że pracujemy z dwoma instancjami bazy danych:

1.    PROD - baza produkcja - OLTP,
2.    REP - baza produkcyjna wykorzystywana do wykonywania raportowania.

Instancja REP jest wykorzystywana jedynie do odczytu - generowania raportów, jest oparta o widoki materializowane i zasilana danymi z instancji PROD co 15 minut.



Widoki materializowane właśnie, potrafią stać się czasami źródłem problemów. Ponieważ każdy widok (z pominięciem tabel tymczasowych) odpowiada jednej tabeli, to w momencie wdrażania nowej wersji systemu, wszystkie widoki, których tabela bazowa się zmieniała muszą zostać odświeżone.



Zdarzyło się nam w przeszłości „zgubić” jedna, czy też dwie zmiany, co doprowadziło do tego, że straciliśmy mnóstwo czasu na ustalenie, czego tak naprawdę brakuje. Pomijając fakt, że samo odświeżanie widoku potrafi zabrać naprawdę sporo czas w wypadku dużych tabel.



Po tym zdarzeniu postanowiliśmy nieco usprawnić nasz proces i przejść z ręcznego dodawania do pliku Excel nazw tabeli, której struktura się zmieniła na proces „pół automatyczny”. Zdecydowaliśmy się na utworzenie triggera śledzącego zmiany modelu danych w naszym środowisku testowym.



Zanim przejdziemy do triggera musimy założyć tabelę, w której będziemy śledzić wszystkie interesujące nas informacje:



CREATE TABLE AUDIT_DDL
(
   OSUSER         VARCHAR2 (255 BYTE),
   HOST           VARCHAR2 (255 BYTE),
   TERMINAL    	  VARCHAR2 (255 BYTE),
   OWNER       	  VARCHAR2 (30 BYTE),
   TYPE        	  VARCHAR2 (30 BYTE),
   NAME        	  VARCHAR2 (30 BYTE),
   SYSEVENT    	  VARCHAR2 (30 BYTE),
   DDL_STATEMENT  CLOB,
   TIMECREATED    DATE
);



Sam trigger wygląda następująco:



CREATE OR REPLACE TRIGGER trg_audit_ddl 
AFTER DDL ON SCHEMA
DECLARE
    vc_stmt     CLOB;
    vt_sql_text ora_name_list_t;
    n           PLS_INTEGER;
BEGIN

    n := ora_sql_txt(vt_sql_text);
    
    -- Zapisujemy sql w CLOBie    
    FOR i IN 1 .. n
    LOOP
        vc_stmt := vc_stmt || vt_sql_text(i);
    END LOOP;
    
    -- Nie interesuja nas zmiany w pakietow, rekompilacje widokow, ani usuwanie danych - truncate.
    IF INSTR(UPPER(ora_dict_obj_type), 'PACKAGE') = 0 AND
       INSTR(UPPER(ora_sysevent), 'TRUNCATE') = 0 AND
       INSTR(UPPER(vc_stmt), 'COMPILE') = 0
    THEN
        
        INSERT INTO audit_ddl (OSUSER, HOST, TERMINAL, OWNER, TYPE, NAME, 
                               SYSEVENT, DDL_STATEMENT, TIMECREATED)
             VALUES (SYS_CONTEXT('USERENV','OS_USER'),
                     SYS_CONTEXT('USERENV','HOST') , 
                     SYS_CONTEXT('USERENV','TERMINAL') ,
                     ora_dict_obj_owner,
                     ora_dict_obj_type,
                     ora_dict_obj_name,
                     ora_sysevent,
                     vc_stmt,
                     SYSDATE
                    );
    END IF;
    
END trg_audit_ddl;
/



Zachęcam do zerknięcia do oficjalnej dokumentacji Oracle (linki zamieszczone są na końcu), która dokładnie opisuje pracę ze zdarzeniami systemowymi/bazy danych. Korzystając z tego podejścia można śledzić naprawdę wiele rozmaitych rzeczy.



Oczywiście po założeniu naszego triggera nasza tabela jest pusta.



SELECT * FROM AUDIT_DDL;



Oracle - Automatyczne śledzenie zmian struktury bazy danych

Teraz wypróbujmy nasz trigger, najpierw tworząc nową tabelę, a następnie modyfikując jaj dodając jedną kolumnę:



CREATE TABLE TRG_TEST 
(
   COL1 VARCHAR2 (10 BYTE)
);



ALTER TABLE TRG_TEST 
   ADD (COL2 NUMBER);



W naszej tabeli AUDIT_DDL powinny pojawić się dwa wiersze:



SELECT * FROM AUDIT_DDL;



Oracle - Automatyczne śledzenie zmian struktury bazy danych

 

Teraz odpowiednio odpytując tabelę AUDIT_DDL możemy sprawdzić dokładnie, co się zmieniło w strukturze naszej bazy pomiędzy wdrożeniami.



Dodatkowa lektura (w języku angielskim)



1. Oracle 11g - http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS2014

2. Oracle 10g - http://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_ev.htm

3. Oracle 9i - http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm

sobota, 17 grudnia 2011, m0rt1m3r

Related Posts Plugin for WordPress, Blogger...

Polecane wpisy





PowerBuilder Tetris
D - Tetris



Programowanie iOS

C# ToolBox

SQL / TSQL / PLSQL ToolBox

Linux / Unix ToolBox





Zaprzyjaznione Strony

Sprite Bandits

Cake Time