오라클 DDL 로그 만들기

DDL 로그 테이블 생성

CREATE TABLE AUDIT_DDL
(
   D              DATE,
   OSUSER         VARCHAR2 (255),
   SESSION_USER   VARCHAR2 (255),
   HOST           VARCHAR2 (255),
   TERMINAL       VARCHAR2 (255),
   MODULE         VARCHAR2 (255),
   OWNER          VARCHAR2 (30),
   TYPE           VARCHAR2 (30),
   NAME           VARCHAR2 (30),
   SYSEVENT       VARCHAR2 (30),
   SQL_TEXT        VARCHAR2 (4000)
);

DDL로그 트리거 생성

create or replace trigger audit_ddl_trg after ddl on database
declare
  sql_text ora_name_list_t;
  stmt VARCHAR2(4000) := '';
  n number;
begin
    n:=ora_sql_txt(sql_text);
    for i in 1..n
    loop
      stmt:=substr(stmt||sql_text(i),1,4000);
    end loop; 
    insert into audit_ddl(d, osuser,session_user,host,terminal,module,owner,type,name,sysevent,sql_txt)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','SESSION_USER') ,
      sys_context('USERENV','HOST') , 
      sys_context('USERENV','TERMINAL') ,
      sys_context('USERENV','MODULE') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent,
      stmt
    );
end;
/