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);
IN_FIRST_PAGE_OTHER:我们要监控的表
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')