Oracle DDL триггер: как получить более подробную информацию о произошедшем событии?

Question

Я хочу сделать историю в базе данных Oracle обо всех операциях DDL, которые выполняются ко времени.

Я создал TABLE и DDL TRIGGER для этого, таким образом:

CREATE TABLE AUDIT_DDL (
  D DATE,
  OSUSER VARCHAR2(255),
  CURRENT_USER VARCHAR2(255),
  HOST VARCHAR2(255),
  TERMINAL VARCHAR2(255),
  OWNER VARCHAR2(30),
  TYPE VARCHAR2(30),
  NAME VARCHAR2(30),
  SYSEVENT VARCHAR2(30));

--/   
CREATE OR REPLACE TRIGGER AUDIT_DDL_TRG AFTER DDL ON SCHEMA 
BEGIN
  IF (ORA_SYSEVENT='TRUNCATE')
  THEN
    NULL;
  ELSE
    INSERT INTO AUDIT_DDL(D, OSUSER,CURRENT_USER,HOST,TERMINAL,OWNER,TYPE,NAME,SYSEVENT)
    VALUES(
      SYSDATE,
      SYS_CONTEXT('USERENV','OS_USER') ,
      SYS_CONTEXT('USERENV','CURRENT_USER') ,
      SYS_CONTEXT('USERENV','HOST') , 
      SYS_CONTEXT('USERENV','TERMINAL') ,
      ORA_DICT_OBJ_OWNER,
      ORA_DICT_OBJ_TYPE,
      ORA_DICT_OBJ_NAME,
      ORA_SYSEVENT
    );
  END IF;
END;
/

Это работает отлично: после каждого оператора DDL у меня появляется новая строка в таблице AUDIT_DDL.

Но у меня до сих пор нет никакой информации о том, какая именно операция была сделана.

Например, следующие два оператора будут производить одинаковые ALTER SYSEVENT в таблице AUDIT_DDL:

ALTER TABLE MYTABLE RENAME COLUMN TEMP TO NEWTEMP;

ALTER TABLE MYTABLE DROP COLUMN NEWTEMP;

Таким образом, таким образом, я не могу знать, какая операция была сделана, а не общая ALTER TABLE, и я даже не могу знать, был ли столбец TEMP переименован или удален из таблицы MYTABLE!

У меня вопрос: как я могу получить дополнительную информацию о событии, произошедшем после выполнения DDL (задействованный объект, детали и т. Д.)?

Answer 1

Проверьте ниже:

сбросить триггер AUDIT_DDL_TRG

drop trigger AUDIT_DDL_TRG

создать новый столбец

 alter table AUDIT_DDL add statements varchar2(1000);

снова запустить курок

CREATE OR REPLACE TRIGGER AUDIT_DDL_TRG
  AFTER DDL ON SCHEMA

DECLARE
  sql_text ora_name_list_t;
  v_stmt   VARCHAR2(2000);
  n        PLS_INTEGER;
BEGIN
  n := ora_sql_txt(sql_text);
  FOR i IN 1 .. n LOOP
    v_stmt := v_stmt || sql_text(i);
  END LOOP;


v_stmt :=regexp_replace(v_stmt,
                                                   'rename[[:space:]]+.*[[:space:]]+to[[:space:]]+([a-z0-9_]+)',
                                                   '\1',
                                                   1,
                                                   1,
                                                   'i');
  IF (ORA_SYSEVENT = 'TRUNCATE') THEN
    NULL;
  ELSE
    INSERT INTO AUDIT_DDL
      (D,
       OSUSER,
       CURRENT_USER,
       HOST,
       TERMINAL,
       OWNER,
       TYPE,
       NAME,
       SYSEVENT,
             statements)
    VALUES
      (SYSDATE,
       SYS_CONTEXT('USERENV', 'OS_USER'),
       SYS_CONTEXT('USERENV', 'CURRENT_USER'),
       SYS_CONTEXT('USERENV', 'HOST'),
       SYS_CONTEXT('USERENV', 'TERMINAL'),
       ORA_DICT_OBJ_OWNER,
       ORA_DICT_OBJ_TYPE,
       ORA_DICT_OBJ_NAME,
       ORA_SYSEVENT,
             v_stmt);
  END IF;
END;
/

сделайте некоторые изменения, и вы увидите заявление