Универсальное журналирование изменений данных в Oracle DB 11g в формате JSON с использованием DML хандлера и Streams

Приложение соединяется с БД через единственную учётную запись. Пользователь проходит процедуру аутентификации через внешнюю службу. В сессию БД должны быть записаны имя пользователя и IP клиента. При изменении данных должны сохраняться сведения о пользователе, о времени изменения, в какой схеме, таблице и колонке изменились данные, тип данных, предыдущие и новые значения.
Для решения этой задачи я использовал 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"}]}

Здесь код операции соответствует таблице AUDIT_ACTIONS.
Для остановки журналирования достаточно выполнить:

EXEC MONITORING_SCHEMA.PAK.DROP_ALL_LOGS;

В журнал можно записывать данные любого типа и размера, но целесообразно длинные строки обрезать до 2000 символов.