oracle - How to Log Alter Column DDL Operations

Question

I need to create a database trigger which will record each alter (Add Column,Modify Column,Drop Column) statements in a specific table using Oracle's schema trigger. How to get it ?

I tried the code below so far :

TRIGGER after_ddl_creation
    after CREATE ON SCHEMA
DECLARE
    V CLOB;
BEGIN
FOR REC IN(SELECT TEXT FROM user_source WHERE  NAME=SYS.DICTIONARY_OBJ_NAME)
 LOOP
   V:=V||REC.TEXT;
 END LOOP ;
 INSERT INTO myAudit VALUES
      (SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_TYPE,SYSDATE,USER,NULL,NULL,V);
    END;

Answer 1

You can use such a database trigger :

create or replace trigger after_ddl_creation after ddl on schema
declare
  v_oty          varchar2(75) := ora_dict_obj_type;
  v_don          varchar2(75) := ora_dict_obj_name;
  v_evt          varchar2(75) := ora_sysevent;
  v_olu          varchar2(75) := nvl(ora_login_user,'Undefined Schema');
  v_sql          ora_name_list_t;
  v_stm          clob;
  v_sct          owa.vc_arr;
  n              pls_integer;
  n_max          pls_integer := 10000; 
 --> can log upto ten-thousand rows of "text" value, within "stmt" column,
 -->         which can be accessed by using (`[user|all|dba]_source`) views.
begin
      v_sct(1) := 'SESSIONID';
      v_sct(2) := 'IP_ADDRESS';
      v_sct(3) := 'TERMINAL';
      v_sct(4) := 'OS_USER';
      v_sct(5) := 'AUTHENTICATION_TYPE';
      v_sct(6) := 'CLIENT_INFO';
      v_sct(7) := 'MODULE';
      for i in 1..7
      loop
       v_sct(i) := sys_context('USERENV',v_sct(i));
      end loop;

      select decode(v_sct(1),0,null,v_sct(1)),
             decode(upper(v_sct(3)),'UNKNOWN',null,v_sct(3)) 
        into v_sct(1),v_sct(3) from dual;
       n := ora_sql_txt( v_sql );
      if n > n_max then
       n := n_max;
      end if;

      for i in 1..n
      loop
       v_stm := v_stm || v_sql(i);
      end loop;

      if ( evt = 'ALTER' and oty = 'TABLE' 
          and regexp_like(v_stm,'Add|Modify|Drop','i') ) then 
       insert into myAudit(ts,usr,evnt,stmt,sessionid,ip,terminal,os_user,auth_type,
                           object_type,object_name,client_info,module_info)
       values(sysdate,v_olu,v_evt,v_stm,v_sct(1),v_sct(2),v_sct(3),v_sct(4),v_sct(5),
              v_oty,v_don,v_sct(6),v_sct(7));
      end if;

end;

by recreating myAudit table due to the above INSERT statement.