自定义审计DDL操作，并记录到表

--自定义数据库表

-- Create table
create table AUDIT_REC
(
  host         VARCHAR2(200),
  "IP ADDRESS" VARCHAR2(200),
  os_log_user  VARCHAR2(200),
  db_user      VARCHAR2(200),
  object_name  VARCHAR2(200),
  action       VARCHAR2(200),
  sql_text     VARCHAR2(200),
  action_time  DATE
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column AUDIT_REC.host is '主机名';
comment on column AUDIT_REC."IP ADDRESS" is 'IP地址';
comment on column AUDIT_REC.os_log_user is '操作系统用户名';
comment on column AUDIT_REC.db_user is '数据库用户名';
comment on column AUDIT_REC.object_name is '数据库对象名';
comment on column AUDIT_REC.action is '操作事件';
comment on column AUDIT_REC.action is 'SQL语句';
comment on column AUDIT_REC.action_time is '操作时间';
 

--DDL触发器

create or replace trigger audit_ddl
  after ddl on database
declare
  sql_text ora_name_list_t;
  n number;
  stmt varchar2(4000) :=null;
begin
  n := ora_sql_txt(sql_text);
  for i in 1 .. n loop
    stmt := stmt || sql_text(i);
  end loop;
  if Ora_sysevent = 'DROP' or Ora_sysevent = 'TRUNCATE' then
    insert into AUDIT_REC
      select SYS_CONTEXT('USERENV', 'TERMINAL'),
             SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
             SYS_CONTEXT('USERENV', 'OS_USER'),
             ora_login_user,
             ora_dict_obj_name,
             ora_sysevent,
             stmt,
             sysdate
        from dual;
  end if;
end;