Oracle 11g 监控单张表的增删改查操作

前言：
线上Oracle数据库有张表的数据有些乱，根据应用db的log和应用的log也没有检查出来谁修改了，所以决定把这张单表做个详细的insert、update、delete监控。一：使用数据库自带的审计功能
1，查看审计功能是否启动
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/app/oracle/admin/power
des/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
SQL>
没有开启审计功能，需要自己去开启一下。
2，开启审计功能
需要用sysdba，注意audit_trail要为DB_EXTENDED才记录执行的具体语句...
alter system set audit_sys_operations=TRUE scope=spfile;
SQL> alter system set audit_sys_operations=TRUE scope=spfile;

System altered.
SQL>再次查看审计功能是否启动
SQL> show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/app/oracle/admin/power
des/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
SQL>
需要重启实例才能看到状态。
3，关闭审计功能
SQL> alter system set audit_trail = none scope=spfile;
4，针对某张表的审计功能
AUDIT UPDATE,DELETE,INSERT ON T_TEST by access;
5，对该张表进行各种DML操作测试
6，查询审计的信息
select EXTENDED_TIMESTAMP,SESSION_ID,SQL_TEXT from DBA_COMMON_AUDIT_TRAIL ORDER BY EXTENDED_TIMESTAMP DESC;
二，采用触发器
看到线上数据库load比以前增加蛮多的，为了单张表的监控开启审计比较消耗资源，有些不划算，所以可以采用另外一种办法来做，就是在表上建立触发器。1，先建立建立测试表：
查看已经建立的表 aaa_test与trig_sql。
SQL> describe plas.aaa_test;
Name Null？ Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2（100）
LOGIN_TIME DATE
SQL>
SQL> describe plas.trig_sql;
Name Null？ Type
----------------------------------------- -------- ----------------------------
LT DATE
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）
SQL>
2，并且在 trig_sql表上面添加索引：
create index idx_time on plas.trig_sql （LT）;

3，建立触发器
create or replace trigger pri_test
after insert or update or delete on plas.aaa_test
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF inserting THEN
INSERT INTO plas.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 plas.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 plas.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，开始进行数据操作测试：
insert into plas.aaa_test1 select 2,"tom",sysdate from dual;
update plas.aaa_test1 a set a.name="tom_up" where a.id=2;
update plas.aaa_test a set a.name="tom_up1" where a.id=1;
......
commit;
5，去查看表记录，会发现如下
SQL> select * from plas.trig_sql;
LT SID SERIAL# USERNAME OSUSER MACHINE TERMINAL PROGRAM SQLTEXT STATUS CLIENT_IP
----------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- -------------------------------- ---------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------------------------------------
2014/10/29 1273 33297 POWERDESK Administrator WORKGROUPWIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; INSERT 192.168.170.180
2014/10/29 1273 33297 POWERDESK Administrator WORKGROUPWIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; DELETE 192.168.170.180
2014/10/29 1273 33297 POWERDESK Administrator WORKGROUPWIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; INSERT 192.168.170.180
2014/10/29 1352 40155 POWERDESK Administrator WORKGROUPWIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; INSERT 192.168.170.180
2014/10/29 1273 33297 POWERDESK Administrator WORKGROUPWIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; UPDATE 192.168.170.180
2014/10/29 1273 33297 POWERDESK Administrator WORKGROUPWIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; UPDATE 192.168.170.110
2014/10/29 25 39527 SYS oracle localhost.localdomain pts/1 sqlplus@localhost.localdomain （TNS V1-V3） update plas.aaa_test a set a.name="tom_update" where id=2 UPDATE
2014/10/29 25 39527 SYS oracle localhost.localdomain pts/1 sqlplus@localhost.localdomain （TNS V1-V3） update plas.aaa_test a set a.name="tom_update3" where id=3 UPDATE
8 rows selected
SQL>
PS：看到SQLTEXT有些都为 begin :id := sys.dbms_transaction.local_transaction_id; end; 的，是因为我执行的insert、delete、update语句在plsqldev.exe客户端执行的，所以没有记录下执行的sql语句。而有些通过sqlplus@localhost.localdomain （TNS V1-V3）客户端连接执行的，会记录下执行过的update语句。
6，统计下当前都有哪些用户以及ip执行了dml操作。
SQL> select username,client_ip from plas.trig_sql group by username,client_ip;
USERNAME CLIENT_IP
------------------------------ ------------------------------------------------------------
PLAS 192.168.170.180
DESKER 192.168.170.110
SQL>在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址