DDL监控脚本

create table OP_OBJ
(
  op_type    VARCHAR2(100),--操作类型
  obj_type   VARCHAR2(100),--对象类型
  obj_name   VARCHAR2(100),--对象名
  obj_user   VARCHAR2(50),--对象所属用户
  op_user    VARCHAR2(100),--操作用户
  op_date    DATE,--操作时间
  op_sqlid   VARCHAR2(50),--操作sql id
  op_sql     VARCHAR2(2000),--操作sql
  obj_source CLOB--操作sql的源语句
);
/


create or replace trigger tri_ddl_obj
  before DDL ON database
--监控ddl对象的操作
declare
  V_SOURCE CLOB;
  v_sqlid  varchar2(100);
  v_sql    varchar2(200);
BEGIN


  --获取create操作的sql id和sql
  begin
    select sql_id, sql_text
      into v_sqlid, v_sql
      from v$sqltext
     where sql_id in
           (select sql_id from v$session where audsid = userenv('sessionid'));
  exception
    when others then
      dbms_output.put_line('1:' || sqlerrm);
  end;


  --获取删除的表的建表语句
  begin
    SELECT dbms_metadata.get_ddl(ora_dict_obj_type,
                                 ora_dict_obj_name,
                                 ora_dict_obj_owner)
      INTO V_SOURCE
      FROM dual;
  exception
    when others then
      dbms_output.put_line('2:' || sqlerrm);
  end;


  if (ORA_SYSEVENT = 'ALTER' and ora_dict_obj_type = 'TABLE' and
     v_sqlid is null) then
    null; --drop TABLE操作会产生1个drop和1个alter事件,此处过滤掉一个
  else
    --插入到监控表中
    insert into op_obj
      (op_type,
       obj_type,
       obj_name,
       obj_user,
       op_user,
       op_date,
       op_sqlid,
       op_sql,
       obj_source)
    values
      ((case when v_sqlid is null and ora_dict_obj_type = 'TABLE' then
        'DROP' else ORA_SYSEVENT end),
       ora_dict_obj_type,
       ora_dict_obj_name,
       ora_dict_obj_owner,
       SYS_CONTEXT('USERENV', 'IP_ADDRESS') || ':' ||
       SYS_CONTEXT('USERENV', 'HOST'), --获取信息可能会不准确
       sysdate,
       v_sqlid,
       v_sql,
       V_SOURCE);
  end if;


exception
  when others then
    dbms_output.put_line('3:' || sqlerrm);
end;