纵有疾风起
人生不言弃

Oracle触发器实现监控某表的CRUD操作


前提:请用sys用户dba权限登录

1.创建一个表来存储操作日志

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),);

2.创建索引(可能已经自动创建,如果已经创建则忽略此步骤)

create index  idx_time on trig_sql (LT);

3.创建触发器

IN_FIRST_PAGE_OTHER:我们要监控的表

create or replace trigger pri_test  after insert or update or delete on IN_FIRST_PAGE_OTHER for each rowDECLARE   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;

4.查询监控记录

对要监控的表进行操作后,可以查到日志信息

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') 

 

文章转载于:https://www.cnblogs.com/lm970585581/p/9138050.html

原著是一个有趣的人,若有侵权,请通知删除

未经允许不得转载:起风网 » Oracle触发器实现监控某表的CRUD操作

分享到: 生成海报
avatar

评论 抢沙发

评论前必须登录!

立即登录   注册

切换注册

登录

忘记密码 ?

切换登录

注册

我们将发送一封验证邮件至你的邮箱, 请正确填写以完成账号注册和激活