Oracle触发器实现监控某表的CRUD操作详解数据库

create table trig_sql( 
         LT DATE not null primary key, 
         SID NUMBER, 
     　 SERIAL#       NUMBER, 
         USERNAME       VARCHAR2(30), 
         OSUSER    VARCHAR2(64), 
         MACHINE       VARCHAR2(32), 
         TERMINAL       VARCHAR2(16), 
         PROGRAM       VARCHAR2(64), 
         SQLTEXT       VARCHAR2(2000), 
         STATUS    VARCHAR2(30), 
         CLIENT_IP       VARCHAR2(60), 
);

create index  idx_time on trig_sql (LT);

create or replace trigger pri_test 
  after insert or update or delete on IN_FIRST_PAGE_OTHER for each row 
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN 
  IF inserting THEN 
    INSERT INTO trig_sql 
        select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, 
               s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, 
               'INSERT', 
              sys_context('userenv','ip_address') 
          from v$sql q, v$session s 
         where s.audsid=(select userenv('SESSIONID') from dual) 
           and s.prev_sql_addr=q.address 
           AND s.PREV_HASH_VALUE = q.hash_value; 
    COMMIT; 
  ELSIF deleting  then 
      INSERT INTO trig_sql 
           select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, 
                       s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, 
                       'DELETE', 
                       sys_context('userenv','ip_address') 
             from v$sql q, v$session s 
            where s.audsid=(select userenv('SESSIONID') from dual) 
             and s.prev_sql_addr=q.address 
             AND s.PREV_HASH_VALUE = q.hash_value; 
    COMMIT; 
  ELSIF updating then 
    INSERT INTO trig_sql 
         select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, 
                     s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, 
                     'UPDATE', 
                     sys_context('userenv','ip_address') 
           from v$sql q, v$session s 
          where s.audsid=(select userenv('SESSIONID') from dual) 
           and s.prev_sql_addr=q.address 
           AND s.PREV_HASH_VALUE = q.hash_value; 
    COMMIT; 
   END IF; 
END;

select t.lt 操作时间, 
       t.sid 会话唯一标识, 
       t.serial# 唯一序列号, 
       t.username 数据库用户,  
       t.osuser 客户端操作系统用户名, 
       t.machine 客户端全名, 
       t.terminal 客户端名, 
       t.program 客户端应用程序, 
       t.sqltext SQL文本, 
       t.status 增删改, 
       t.client_ip IP地址 from trig_sql t   where  
       to_char(t.lt, 'yyyy-mm-dd hh24:mi:ss')      
BETWEEN   to_char(TO_DATE('2018-06-01 16:42:10','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')  
AND to_char(TO_DATE('2018-06-01 16:42:11','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')