oracle 数据库的ddl监控脚本的改善

create table tb_db_ddl_mobitor
(       ddl_time    date,
        session_id  varchar2(100) ,
        os_user    varchar2(100) ,
        ip_address  varchar2(100),
        terminal   varchar2(100) ,
        host       varchar2(100) ,
        user_name  varchar2(100) ,
        ddl_type    varchar2(100),
        object_type varchar2(100),
        owner      varchar2(100) ,
        object_name varchar2(100),
        sql_text varchar2(100)  
      ) tablespace CENTERDBT;

create or replace trigger tri_ddl_monitor
  before create or alter or drop or truncate on database
declare
  ipaddr      varchar2(30);
  stext       varchar2(4000);
  v_sessionid varchar2(50);
begin
  if ora_sysevent in ('CREATE', 'ALTER','DROP', 'TRUNCATE') then
    select sys_context('USERENV', 'SESSIONID') into v_sessionid from dual;
    select distinct t3.sql_text
      into stext
      from v$session t1, v$sql t3
     where t1.SQL_ID = t3.SQL_ID
       and t1.AUDSID = v_sessionid;
    insert into tb_db_ddl_mobitor
      (ddl_time,
       session_id,
       os_user,
       ip_address,
       terminal,
       host,
       user_name,
       ddl_type,
       object_type,
       owner,
       object_name,
       sql_text)
    values
      (sysdate,
       sys_context('USERENV', 'SESSIONID'),
       sys_context('USERENV', 'OS_USER'),
       sys_context('USERENV', 'IP_ADDRESS'),
       sys_context('USERENV', 'TERMINAL'),
       sys_context('USERENV', 'HOST'),
       ora_login_user,
       ora_sysevent,
       ora_dict_obj_type,
       ora_dict_obj_owner,
       ora_dict_obj_name,
       stext);
    --commit;
 
  end if;
exception
  when no_data_found then
    null;
end;