Logiranje izmjene strukture baze - DDL LOGGER
April 11, 2013 – mradovanModeliranje baze podataka često nije jednokratan posao, uvijek se nađe potreba da se naprave izmjene na dijelovima koda ili strukturi tablica, doda ili oduzme neka kolona u nekoj tablici, izmijeni tip podatka neke kolone, doda ili obriše neki ključ ili index…
Kada se takve izmjene rade nepredviđeno, a ne dokumentiraju se, ili u okolini gdje takve stvari radi više ljudi (namjerno ili slučajno), i kad se to radi ručno kroz komandnu liniju a bez modeliranja u nekom alatu za modeliranje (čiji bi se modeli spremali pod sistemom verzioniranja), mogu dovesti do vrlo nezgodnih situacija u kojima ne znamo tok događaja - tko je, kada i što napravio (i zašto ?).
Čak sam i sam bio u situacijama gdje neke promjene radim na razvoju, pa nakon nekog vremena ne znam šta sam napravio i kojim redoslijedom, ili zaključim da sam otišao pogrešnim putem i da se trebam vratiti nekoliko koraka unatrag, a ne znam točno kako…
Rješenje je vrlo jednostavno - database trigger koji logira sistemske događaje u tablicu DDLOG sa sljedećim kolonama:
- OWNER (vlasnik objekta)
- OPERATION (CREATE, ALTER ili DROP)
- OBJECT_TYPE (procedure, table, trigger,package…)
- OBJECT_NAME (ime objekta)
- SQL_TEXT (prvih 1000 znakova sql izraza)
- SQL_FULLTEXT (kompletni kod izraza)
- CREATED (vrijeme upisa)
- CREATED_BY (user koji je pokrenuo operaciju)
- TERMINAL_NAME (ime računala s kojeg je operacija pokrenuta)
- TERMINAL_IP (IP adresa računala s kojeg je operacija pokrenuta)
- SESSIONID (ID sesije u kojoj je operacija izvršena).
Iz drugih korisničkih shema se može raditi select na ovu tablicu uz posjedovanje SELECT ANY TABLE privilegije.
Druga tablica ERRLOG služi za logiranje eventualnih grešaka u samom triggeru.
Priložena je skripta koju je potrebno pokrenuti pod SYS userom, čime se kreira user DDLOGGER, sa tablicom DDLOG:
/********************************************************************************************************************************************
Desc: User for logging changes on database objects: CREATE, ALTER and DROP operations.
Author: Mihael Radovan
Last change: 28.03.2013
********************************************************************************************************************************************/
/* user with privileges */
CREATE user ddlogger IDENTIFIED BY DDL;
GRANT UNLIMITED TABLESPACE TO ddlogger;
/* error log table */
CREATE TABLE ddlogger.errlog
(tstamp TIMESTAMP DEFAULT systimestamp,
errcode number,
errtext varchar2(500))
NOLOGGING, COMPRESS FOR oltp, NOCACHE, NOMONITORING;
/* err log procedure */
CREATE OR REPLACE PROCEDURE ddlogger.plog(errcode number, errtext varchar2)
IS
PRAGMA autonomous_transaction;
BEGIN
INSERT /*+ append */ INTO ddlogger.errlog(errcode, errtext)
VALUES (errcode, errtext);
COMMIT;
END plog;
/
/* ddl log table */
CREATE TABLE ddlogger.ddlog (
owner VARCHAR2(30),
operation VARCHAR2(30),
object_type VARCHAR2(30),
object_name VARCHAR2(30),
sql_text VARCHAR2(1000),
sql_fulltext clob,
created TIMESTAMP,
created_by VARCHAR2(30),
terminal_name VARCHAR2(255),
terminal_ip VARCHAR2(30),
sessionid number)
NOLOGGING, COMPRESS FOR oltp, NOCACHE, NOMONITORING;
/* database trigger */
CREATE OR REPLACE TRIGGER ddlogger.DDL_TRIGGER BEFORE DDL ON DATABASE
DECLARE
oper varchar2(30);
l_sql_text ora_name_list_t;
l_count NUMBER;
l_sql varchar2(1000);
errcode number;
errtext varchar2(500);
BEGIN
SELECT ora_sysevent
INTO oper
FROM DUAL;
l_count := ora_sql_txt(l_sql_text);
FOR i IN 1..l_count
LOOP
l_sql := l_sql||l_sql_text(i);
END LOOP;
IF (oper IN ('CREATE', 'ALTER', 'DROP')) AND (ora_dict_obj_name != ' ddlog') THEN NULL;
INSERT /*+ append */ INTO ddlogger.ddlog (owner, operation, object_type, object_name, sql_text, sql_fulltext, created, created_by, terminal_name, terminal_ip, sessionid)
VALUES (ora_dict_obj_owner, oper, ora_dict_obj_type, ora_dict_obj_name, substr(l_sql, 1, 1000), l_sql, systimestamp, user, SYS_CONTEXT ('userenv', 'host'), SYS_CONTEXT ('userenv', 'ip_address'), SYS_CONTEXT ('userenv', 'sessionid'));
END IF;
EXCEPTION
WHEN others THEN
errcode:= SQLCODE;
errtext:= SQLERRM;
plog(errcode, errtext);
END;
/
