Приложение соединяется с БД через единственную учётную запись. Пользователь проходит процедуру аутентификации через внешнюю службу. В сессию БД должны быть записаны имя пользователя и IP клиента. При изменении данных должны сохраняться сведения о пользователе, о времени изменения, в какой схеме, таблице и колонке изменились данные, тип данных, предыдущие и новые значения.
Для решения этой задачи я использовал PL/JSON.
Для начала я перешёл в папку с инсталлятором PL/JSON, запустил sqlplus, создал пользователя, перешёл в его схему и установил PL/JSON:
Здесь код операции соответствует таблице AUDIT_ACTIONS.
Для остановки журналирования достаточно выполнить:
Для решения этой задачи я использовал PL/JSON.
Для начала я перешёл в папку с инсталлятором PL/JSON, запустил sqlplus, создал пользователя, перешёл в его схему и установил PL/JSON:
sqlplus system/password@ORABASE CREATE USER MONITORING_SCHEMA IDENTIFIED BY tiger ACCOUNT LOCK; ALTER SESSION SET CURRENT_SCHEMA = MONITORING_SCHEMA; @install.sqlСоздал три таблицы: две для тестирования и последняя — для журналирования. И последовательности с триггерами для каждой таблицы.
--DROP TABLE MONITORING_SCHEMA.TPROGRAM CASCADE CONSTRAINTS PURGE; --DROP SEQUENCE MONITORING_SCHEMA.SQ_TPROGRAM; CREATE SEQUENCE MONITORING_SCHEMA.SQ_TPROGRAM MINVALUE 1000; CREATE TABLE MONITORING_SCHEMA.TPROGRAM ( IDPROGRAM INTEGER NOT NULL, CVAL CHAR(1 BYTE), VCVAL VARCHAR2(254 CHAR), NUMVAL NUMBER, DATEVAL DATE, TSVAL TIMESTAMP, TSTZVAL TIMESTAMP WITH TIME ZONE, TSLTZVAL TIMESTAMP WITH LOCAL TIME ZONE, IYVAL INTERVAL YEAR TO MONTH, IDVAL INTERVAL DAY TO SECOND, CONSTRAINT PK_TPROGRAMM PRIMARY KEY (IDPROGRAM) ); CREATE OR REPLACE TRIGGER MONITORING_SCHEMA.PK_TPROGRAM BEFORE INSERT ON MONITORING_SCHEMA.TPROGRAM FOR EACH ROW BEGIN IF :NEW.IDPROGRAM IS NULL THEN SELECT MONITORING_SCHEMA.SQ_TPROGRAM.NEXTVAL INTO :NEW.IDPROGRAM FROM DUAL; END IF; END; / --DROP TABLE MONITORING_SCHEMA.TSUBPROGRAM CASCADE CONSTRAINTS PURGE; --DROP SEQUENCE MONITORING_SCHEMA.SQ_TSUBPROGRAM; CREATE SEQUENCE MONITORING_SCHEMA.SQ_TSUBPROGRAM MINVALUE 1000; CREATE TABLE MONITORING_SCHEMA.TSUBPROGRAM ( IDSUBPROGRAM INTEGER NOT NULL, NCVAL NCHAR(2), NVCVAL NVARCHAR2(2000), CLOBVAL CLOB, NCLOBVAL NCLOB, BLOBVAL BLOB, CONSTRAINT PK_TSUBPROGRAMM PRIMARY KEY (IDSUBPROGRAM) ); CREATE OR REPLACE TRIGGER MONITORING_SCHEMA.PK_TSUBPROGRAM BEFORE INSERT ON MONITORING_SCHEMA.TSUBPROGRAM FOR EACH ROW BEGIN IF :NEW.IDSUBPROGRAM IS NULL THEN SELECT MONITORING_SCHEMA.SQ_TSUBPROGRAM.NEXTVAL INTO :NEW.IDSUBPROGRAM FROM DUAL; END IF; END; / --DROP TABLE MONITORING_SCHEMA.TAUDIT_LOG CASCADE CONSTRAINTS PURGE; --DROP SEQUENCE MONITORING_SCHEMA.SQ_TAUDIT_LOG; CREATE SEQUENCE MONITORING_SCHEMA.SQ_TAUDIT_LOG MINVALUE 1000; CREATE TABLE MONITORING_SCHEMA.TAUDIT_LOG ( IDAUDIT_LOG INTEGER NOT NULL, TABLE_OWNER VARCHAR2(30 BYTE) NOT NULL, TABLE_NAME VARCHAR2(30 BYTE) NOT NULL, SESSION_USER VARCHAR2(100 CHAR) NOT NULL, LOG_WHEN TIMESTAMP WITH LOCAL TIME ZONE DEFAULT LOCALTIMESTAMP NOT NULL, OPERATION INTEGER NOT NULL, LOG_VALUES CLOB, CONSTRAINT PK_TAUDIT_LOG PRIMARY KEY (IDAUDIT_LOG) ); CREATE OR REPLACE TRIGGER MONITORING_SCHEMA.PK_TAUDIT_LOG BEFORE INSERT ON MONITORING_SCHEMA.TAUDIT_LOG FOR EACH ROW BEGIN IF :NEW.IDAUDIT_LOG IS NULL THEN SELECT MONITORING_SCHEMA.SQ_TAUDIT_LOG.NEXTVAL INTO :NEW.IDAUDIT_LOG FROM DUAL; END IF; END; /Предоставил разрешения для компиляции пакета журналирования:
CONN SYS / AS SYSDBA GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO MONITORING_SCHEMA; GRANT EXECUTE ON SYS.DBMS_STREAMS_AUTH TO MONITORING_SCHEMA; GRANT EXECUTE ON SYS.DBMS_STREAMS_ADM TO MONITORING_SCHEMA; GRANT EXECUTE ON SYS.DBMS_APPLY_ADM TO MONITORING_SCHEMA; GRANT EXECUTE ON SYS.DBMS_CAPTURE_ADM TO MONITORING_SCHEMA; GRANT EXECUTE ON SYS.DBMS_STREAMS TO MONITORING_SCHEMA; GRANT SELECT ON SYS.DBA_APPLY_INSTANTIATED_OBJECTS TO MONITORING_SCHEMA; EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'system', grant_privileges => TRUE); CONN SYSTEMСкомппилировал пакет:
CREATE OR REPLACE PACKAGE MONITORING_SCHEMA.PAK AUTHID CURRENT_USER AS TYPE TABLELIST IS VARRAY(100) OF VARCHAR2(30); PROCEDURE CREATE_LOG(schema IN VARCHAR2, tables IN TABLELIST); PROCEDURE DROP_ALL_LOGS; PROCEDURE DML_HANDLER(in_any IN ANYDATA); END; / CREATE OR REPLACE PACKAGE BODY MONITORING_SCHEMA.PAK AS PROCEDURE CREATE_LOG(schema IN VARCHAR2, tables IN TABLELIST) AS database_name global_name.global_name%TYPE; obj_name VARCHAR2(65); scn NUMBER; BEGIN SELECT GLOBAL_NAME INTO database_name FROM GLOBAL_NAME; scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_STREAMS_ADM.SET_UP_QUEUE(); FOR i IN tables.FIRST..tables.LAST LOOP obj_name := schema||'.'||tables(i); DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name => obj_name, streams_type => 'capture', streams_name => 'capture_stream', include_tagged_lcr => TRUE); DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name => obj_name, streams_type => 'apply', streams_name => 'apply_stream', include_tagged_lcr => TRUE); DBMS_APPLY_ADM.SET_DML_HANDLER(object_name => obj_name, object_type => 'TABLE', operation_name => 'DEFAULT', user_procedure => 'MONITORING_SCHEMA.PAK.DML_HANDLER', apply_name => 'apply_stream', assemble_lobs => FALSE); DBMS_APPLY_ADM.SET_DML_HANDLER(object_name => obj_name, object_type => 'TABLE', operation_name => 'LOB_UPDATE', user_procedure => 'MONITORING_SCHEMA.PAK.DML_HANDLER', apply_name => 'apply_stream', assemble_lobs => FALSE); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => obj_name, source_database_name => database_name, instantiation_scn => scn); END LOOP; DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'capture_stream'); DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'apply_stream', parameter => 'DISABLE_ON_ERROR', value => 'N'); DBMS_APPLY_ADM.START_APPLY(apply_name => 'apply_stream'); END; PROCEDURE DROP_ALL_LOGS AS database_name global_name.global_name%TYPE; obj_name VARCHAR2(65); BEGIN SELECT GLOBAL_NAME INTO database_name FROM GLOBAL_NAME; DBMS_APPLY_ADM.ALTER_APPLY(apply_name => 'apply_stream', remove_apply_tag => TRUE); DBMS_APPLY_ADM.STOP_APPLY(apply_name => 'apply_stream'); DBMS_APPLY_ADM.DELETE_ALL_ERRORS('apply_stream'); DBMS_APPLY_ADM.DROP_APPLY('apply_stream'); DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => 'capture_stream'); DBMS_CAPTURE_ADM.DROP_CAPTURE('capture_stream'); FOR rec IN (SELECT SOURCE_OBJECT_OWNER, SOURCE_OBJECT_NAME FROM DBA_APPLY_INSTANTIATED_OBJECTS WHERE SOURCE_DATABASE=database_name AND SOURCE_OBJECT_TYPE='TABLE') LOOP obj_name := rec.SOURCE_OBJECT_OWNER||'.'||rec.SOURCE_OBJECT_NAME; DBMS_APPLY_ADM.SET_DML_HANDLER(object_name => obj_name, object_type => 'TABLE', operation_name => 'DEFAULT', error_handler => FALSE, user_procedure => NULL, apply_name => 'apply_stream'); DBMS_APPLY_ADM.SET_DML_HANDLER(object_name => obj_name, object_type => 'TABLE', operation_name => 'LOB_UPDATE', error_handler => FALSE, user_procedure => NULL, apply_name => 'apply_stream'); DBMS_CAPTURE_ADM.ABORT_TABLE_INSTANTIATION(table_name => obj_name); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => obj_name, source_database_name => database_name, instantiation_scn => NULL); END LOOP; DBMS_STREAMS_ADM.REMOVE_QUEUE('streams_queue', true, true); END; FUNCTION getValues(xml IN XMLType, kind IN VARCHAR2, lcr IN SYS.LCR$_ROW_RECORD) RETURN json_list AS res json_list := json_list(); item json; doc DBMS_XMLDOM.DOMDocument; nl1 DBMS_XMLDOM.DOMNodeList; nl2 DBMS_XMLDOM.DOMNodeList; n1 DBMS_XMLDOM.DOMNode; n2 DBMS_XMLDOM.DOMNode; column_name VARCHAR2(30); data_type VARCHAR2(30); ch VARCHAR2(2000 CHAR); ret PLS_INTEGER; nc NCHAR(1000); nvc NVARCHAR2(2000); BEGIN doc := DBMS_XMLDOM.newDOMDocument(xml.EXTRACT('/ROW_LCR/'||kind||'_values')); nl1 := DBMS_XMLDOM.getElementsByTagName(DBMS_XMLDOM.getDocumentElement(doc), kind||'_value'); FOR i IN 0 .. DBMS_XMLDOM.getLength(nl1) - 1 LOOP n1 := DBMS_XMLDOM.item(nl1, i); nl2 := DBMS_XMLDOM.getChildNodes(n1); item := json(); FOR j IN 0 .. DBMS_XMLDOM.getLength(nl2) - 1 LOOP n2 := DBMS_XMLDOM.item(nl2, j); IF DBMS_XMLDOM.getNodeName(n2) = 'column_name' THEN column_name := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(n2)); item.put('column_name', column_name); ELSIF DBMS_XMLDOM.getNodeName(n2) = 'data' THEN data_type := DBMS_XMLDOM.getNodeName(DBMS_XMLDOM.getFirstChild(n2)); IF data_type = 'number' THEN ch := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(DBMS_XMLDOM.getFirstChild(n2))); item.put(data_type, cast(ch as number)); ELSIF data_type IN ('date', 'timestamp', 'timestamp_tz', 'timestamp_ltz') THEN ch := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild( DBMS_XMLDOM.getFirstChild(DBMS_XMLDOM.getFirstChild(n2)))); item.put(data_type, ch); ELSIF data_type = 'nchar' THEN ret := lcr.GET_VALUE(kind, column_name).GETNCHAR(nc); item.put(data_type, TRIM(TO_CHAR(nc))); ELSIF data_type = 'nvarchar2' THEN ret := lcr.GET_VALUE(kind, column_name).GETNVARCHAR2(nvc); item.put(data_type, TO_CHAR(nvc)); ELSE ch := SUBSTR(DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(DBMS_XMLDOM.getFirstChild(n2))), 1, 2000); item.put(data_type, ch); END IF; res.append(item.to_json_value); END IF; END LOOP; END LOOP; RETURN res; END; PROCEDURE DML_HANDLER(in_any IN ANYDATA) AS lcr SYS.LCR$_ROW_RECORD; rc PLS_INTEGER; command VARCHAR2(32); oper PLS_INTEGER; towner VARCHAR2(30); tname VARCHAR2(30); login VARCHAR2(100); xml XMLTYPE; log_val json := json(); clb clob; msg VARCHAR2(500); BEGIN JSON_PRINTER.ascii_output := FALSE; rc := in_any.GETOBJECT(lcr); command := lcr.GET_COMMAND_TYPE(); SELECT ACTION INTO oper FROM AUDIT_ACTIONS WHERE NAME=command; towner := lcr.GET_OBJECT_OWNER(); tname := lcr.GET_OBJECT_NAME(); login := NVL(UTL_RAW.CAST_TO_VARCHAR2(lcr.GET_TAG()), 'UNKNOWN'); xml := DBMS_STREAMS.CONVERT_LCR_TO_XML(in_any); SELECT DELETEXML(xml, '//data/*[name()=''nchar'' or name()=''nvarchar2'']/node()') INTO xml FROM DUAL; IF command IN ('DELETE', 'UPDATE') THEN log_val.put('old_values', getValues(xml, 'old', lcr)); END IF; IF command IN ('INSERT', 'UPDATE') THEN log_val.put('new_values', getValues(xml, 'new', lcr)); END IF; clb := empty_clob(); DBMS_LOB.CREATETEMPORARY(clb, TRUE); log_val.to_clob(clb); INSERT INTO MONITORING_SCHEMA.TAUDIT_LOG(IDAUDIT_LOG, TABLE_OWNER, TABLE_NAME, SESSION_USER, LOG_WHEN, OPERATION, LOG_VALUES) VALUES (MONITORING_SCHEMA.SQ_TAUDIT_LOG.NEXTVAL, towner, tname, login, LOCALTIMESTAMP, oper, clb); DBMS_LOB.FREETEMPORARY(clb); EXCEPTION WHEN OTHERS THEN msg := SUBSTR(SQLERRM, 1, 500); INSERT INTO MONITORING_SCHEMA.TAUDIT_LOG(IDAUDIT_LOG, TABLE_OWNER, TABLE_NAME, SESSION_USER, LOG_WHEN, OPERATION, LOG_VALUES) VALUES (MONITORING_SCHEMA.SQ_TAUDIT_LOG.NEXTVAL, towner, tname, login, LOCALTIMESTAMP, oper, TO_CLOB(msg)); END; END; /Запустил журналирование изменений для двух таблиц:
EXEC MONITORING_SCHEMA.PAK.CREATE_LOG('MONITORING_SCHEMA', MONITORING_SCHEMA.PAK.TABLELIST('TPROGRAM', 'TSUBPROGRAM'));Можно проверить, что журналирование запущено:
SELECT * FROM DBA_STREAMS_ADMINISTRATOR; SELECT * FROM DBA_STREAMS_TABLE_RULES; SELECT * FROM DBA_CAPTURE; SELECT * FROM DBA_CAPTURE_PREPARED_TABLES; SELECT * FROM DBA_APPLY; SELECT * FROM DBA_APPLY_INSTANTIATED_OBJECTS; SELECT * FROM DBA_APPLY_DML_HANDLERS;Для тестирования выполнил некоторые изменения данных, предварительно сохранив в сессии данные пользователя:
EXEC DBMS_STREAMS.SET_TAG(tag => UTL_RAW.CAST_TO_RAW('Иванов: 192.168.0.35')); INSERT INTO MONITORING_SCHEMA.TPROGRAM (CVAL, VCVAL, NUMVAL, DATEVAL, TSVAL, TSTZVAL, TSLTZVAL, IYVAL, IDVAL) VALUES ('A', 'Двадцать', 20, SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP, LOCALTIMESTAMP, TO_YMINTERVAL('01-02'), TO_DSINTERVAL('2 10:20:30.456')); COMMIT; UPDATE MONITORING_SCHEMA.TPROGRAM SET VCVAL='Тридцать', NUMVAL=30; COMMIT; DELETE FROM MONITORING_SCHEMA.TPROGRAM; COMMIT; CREATE OR REPLACE DIRECTORY IMG AS '/u01/app/oracle/databases/img'; DECLARE l_blob BLOB; l_bfile BFILE := BFILENAME('IMG', 'img01.png'); BEGIN INSERT INTO MONITORING_SCHEMA.TSUBPROGRAM (CLOBVAL, NCLOBVAL, BLOBVAL) VALUES ('Текст CLOB', N'Текст NCLOB', empty_blob()) RETURNING BLOBVAL INTO l_blob; DBMS_LOB.OPEN(l_bfile, DBMS_LOB.LOB_READONLY); DBMS_LOB.LOADFROMFILE(l_blob, l_bfile, DBMS_LOB.GETLENGTH(l_bfile)); DBMS_LOB.CLOSE(l_bfile); COMMIT; END; / DROP DIRECTORY IMG; UPDATE MONITORING_SCHEMA.TSUBPROGRAM SET NCVAL=N'Ру', NVCVAL=N'Текст NVARCHAR2'; COMMIT; DELETE FROM MONITORING_SCHEMA.TSUBPROGRAM; COMMIT;В таблицу TAUDIT_LOG добавились строки:
IDAUDIT_LOG | TABLE_OWNER | TABLE_NAME | SESSION_USER | LOG_WHEN | OPERATION | LOG_VALUES |
---|---|---|---|---|---|---|
1000 | MONITORING_SCHEMA | TSUBPROGRAM | Иванов: 192.168.0.35 | 08.08.14 08:39:13,761547000 | 2 | {"new_values":[{"column_name":"IDSUBPROGRAM","number":1000},{"column_name":"NCVAL","nchar":""},{"column_name":"NVCVAL","nvarchar2":""},{"column_name":"CLOBVAL","varchar2":""},{"column_name":"NCLOBVAL","raw":""},{"column_name":"BLOBVAL","raw":""}]} |
1001 | MONITORING_SCHEMA | TPROGRAM | Иванов: 192.168.0.35 | 08.08.14 08:39:13,762111000 | 2 | {"new_values":[{"column_name":"IDPROGRAM","number":1000},{"column_name":"CVAL","char":"A"},{"column_name":"VCVAL","varchar2":"Двадцать"},{"column_name":"NUMVAL","number":20},{"column_name":"DATEVAL","date":" 2014/08/08 08:39:13"},{"column_name":"TSVAL","timestamp":" 2014/08/08 08:39:13,463334000"},{"column_name":"TSTZVAL","timestamp_tz":" 2014/08/08 08:39:13,463336000 EUROPE/MOSCOW"},{"column_name":"TSLTZVAL","timestamp_ltz":" 2014/08/08 04:39:13,463336000"},{"column_name":"IYVAL","interval_ym":"+000000001-02"},{"column_name":"IDVAL","interval_ds":"+000000002 10:20:30.456000000"}]} |
1002 | MONITORING_SCHEMA | TSUBPROGRAM | Иванов: 192.168.0.35 | 08.08.14 08:39:13,766396000 | 6 | {"old_values":[{"column_name":"IDSUBPROGRAM","number":1000},{"column_name":"NCVAL","nchar":""},{"column_name":"NVCVAL","nvarchar2":""}],"new_values":[{"column_name":"CLOBVAL","varchar2":"Текст CLOB"},{"column_name":"NCLOBVAL","nvarchar2":"Текст NCLOB"}]} |
1003 | MONITORING_SCHEMA | TPROGRAM | Иванов: 192.168.0.35 | 08.08.14 08:39:13,767247000 | 6 | {"old_values":[{"column_name":"IDPROGRAM","number":1000},{"column_name":"VCVAL","varchar2":"Двадцать"},{"column_name":"NUMVAL","number":20}],"new_values":[{"column_name":"VCVAL","varchar2":"Тридцать"},{"column_name":"NUMVAL","number":30}]} |
1004 | MONITORING_SCHEMA | TSUBPROGRAM | Иванов: 192.168.0.35 | 08.08.14 08:39:13,770783000 | 6 | {"old_values":[{"column_name":"IDSUBPROGRAM","number":1000}],"new_values":[{"column_name":"BLOBVAL","raw":"89504E470D0A1A0A0000000D494844520000001C0000001C080300000045D32FA60000001974455874536F6674776172650041646F626520496D616765526561647971C9653C00000300504C54458C6244895D3B331919FFFFA5491D13FFFF97FFFF8732120AACA2A190623E3432197C63313C080B968541EEE672FFFF8D5A5A5A3D32322A0000D6C964FFFFFF62322150211241150C3B2B2BB1A355DBD6D52018188E623EBBA552C6B65A927D3D7E5133A18C46AD954B250502968141735A2DAF994D74472D4924137E5336130101B7A955C6BEBD5148485F2E21784C30A9974BFFFF9EFFFF9C865A394A3D3C2A2121634626300003694D27C3BBBA746B6B6E542BB5ACAB7C562E4A40402C0C07080D152C0C0D8E7A42855939522D118A5E3C544A4A332929352E2E0B0101602F215325171B1B244239395050510000003B060A815536875B3AFFFF94FFFF7D5A2F1E8256365D371EFFFF80FFFF7EFFFF91835737FFFF7FFFFF93FFFF84FFFF8F43180EFFFF85FFFF99FFFF92FFFF8EFFFF95FFFF8B825637FFFF8AFFFF88A28E47D2CCCBE9E6E58153345E3320FFFF81FFFFACDDD0698458375939258B81818A5E3BFFFF90FFFF985D371C97863DD1BB5EFFFB80E1DDDCC3B65BFFFF838F674A3D43265E512A3E0D0BE9E26E8F84845E5E5EECE578FFFF9A8453338A7A446060604F2E22B3A35C120B15C5B75B00000D201010663823C0B7B7463A20755A31A994474C5254A38B789F8B4B1A1111A1806BC2B060CBB6685B6163C3BCBA9C8775856331856F37A38642A48E49FFFDEF3A3A3D584E4EF6EE803E0900917D42C7B95C7A7070C9B95D43494DCFBC684B4B4BB9B0AFAA9048AD974CF1EEEEFAF679DFD770D0CAC8B1B665D3CDCCD4CECE8C5D3BA599998C5F3CCCC5C4634027654D25C5CC63403535845737E6E2E1FDFA84968246F0E97D524745BDB4B4434321DED9D8A58773DFE076A98C795B301EFFFFA1FDFDFDDAD26E78783B9E949452585A9A8D8E86723583703C8B7239B29F5961381E582C18C1B8B8C2BAB8572A19A08B456F666637170C3A1D0D4C413FF6F280B3A9A8CFCF6BEAE174F5F07542221E906748966A43C5B55B93803FD4C763867C7C5D4124070D08545428E7DF75785E307F5930453B3A463D3DA49048A6954AF5EF78545454EEE67BE8E177F7F780FFFFFFC9049A930000010074524E53FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF"}]} |
1005 | MONITORING_SCHEMA | TPROGRAM | Иванов: 192.168.0.35 | 08.08.14 08:39:13,775046000 | 7 | {"old_values":[{"column_name":"IDPROGRAM","number":1000},{"column_name":"CVAL","char":"A"},{"column_name":"VCVAL","varchar2":"Тридцать"},{"column_name":"NUMVAL","number":30},{"column_name":"DATEVAL","date":" 2014/08/08 08:39:13"},{"column_name":"TSVAL","timestamp":" 2014/08/08 08:39:13,463334000"},{"column_name":"TSTZVAL","timestamp_tz":" 2014/08/08 08:39:13,463336000 EUROPE/MOSCOW"},{"column_name":"TSLTZVAL","timestamp_ltz":" 2014/08/08 04:39:13,463336000"},{"column_name":"IYVAL","interval_ym":"+000000001-02"},{"column_name":"IDVAL","interval_ds":"+000000002 10:20:30.456000000"}]} |
1006 | MONITORING_SCHEMA | TSUBPROGRAM | Иванов: 192.168.0.35 | 08.08.14 08:39:13,776091000 | 6 | {"old_values":[{"column_name":"IDSUBPROGRAM","number":1000},{"column_name":"NCVAL","nchar":""},{"column_name":"NVCVAL","nvarchar2":""}],"new_values":[{"column_name":"NCVAL","nchar":"Ру"},{"column_name":"NVCVAL","nvarchar2":"Текст NVARCHAR2"}]} |
1007 | MONITORING_SCHEMA | TSUBPROGRAM | Иванов: 192.168.0.35 | 08.08.14 08:39:13,779515000 | 7 | {"old_values":[{"column_name":"IDSUBPROGRAM","number":1000},{"column_name":"NCVAL","nchar":"Ру"},{"column_name":"NVCVAL","nvarchar2":"Текст NVARCHAR2"}]} |
Для остановки журналирования достаточно выполнить:
EXEC MONITORING_SCHEMA.PAK.DROP_ALL_LOGS;В журнал можно записывать данные любого типа и размера, но целесообразно длинные строки обрезать до 2000 символов.
Комментариев нет:
Отправить комментарий