[20140529]使用审计记录一些不能DML的记录.txt
--前几天看了审计的一些内容，突然想起多年以前一直困扰我的问题。
--生产系统存在一个问题,就是录入信息存在大于的信息超出范围的问题,出现这个问题,主要是程序存在单位的转换,
--比如是mg的单位，开成了g的单位。这样在转化的过程中出现溢出情况,本来这些问题应该有程序来解决,可惜开发
--一直不去纠正这个问题,我们的做法是要求用户选取一段记录来保存,或者逐条保存.显然这样效率不高,用户抱怨不断.
--实际上我对这样的开发团队非常失望!
--我本来考虑通过触发器来判断,实际上根本不可行，具体细节我已经忘记。今天看了一下11G的审计,突然想到一个更
--简单的方法,做一个例子:
SYS@test> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ---------------------------------------- ---------
audit_trail string DB
--因为要记录修改的sql语句，必须加入EXTENDED，遇到一点小插曲，可以参考链接：
http://blog.itpub.net/267265/viewspace-1172182/
SYS@test> alter system set audit_trail=db,extended scope=spfile ;
System altered.
--必须重启在生效！
SYS@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SYS@test> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ---------------------------------------- --------------
audit_trail string DB, EXTENDED
SCOTT@test01p> create table dept1 as select * from dept;
Table created.
SCOTT@test01p> @desc dept1 ;
Name Null? Type
----------------------- -------- ----------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SCOTT@test> insert into dept1 values (100,'a','b');
insert into dept1 values (100,'a','b')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
--100超出了范围,出现ora-01438错误.当然可以建立这样的事件来跟踪问题.
--缺点比如ora-00001也要另外写一个事件.
--SQL> alter system set events='1438 trace name Errorstack forever,level 10';
--选取不成功的insert作为记录,这样记录就很少,不会太多,对系统影响也最小.
SCOTT@test> audit insert on dept1 whenever not successful;
Audit succeeded.
SCOTT@test> insert into dept1 values (140,'a','b');
insert into dept1 values (140,'a','b')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SCOTT@test> select sql_text,sql_bind,returncode from dba_audit_trail where owner=user and obj_name='DEPT1';
SQL_TEXT SQL_BIND RETURNCODE
------------------------------------------------------------ -------------------------------------------------- ----------
insert into dept1 values (140,'a','b') 1438
--插入绑定变量看看:
SCOTT@test> var n number;
SCOTT@test> exec :n :=100;
PL/SQL procedure successfully completed.
SCOTT@test> insert into dept1 values (:n,'a','b');
insert into dept1 values (:n,'a','b')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SCOTT@test> select sql_text,sql_bind,returncode from dba_audit_trail where owner=user and obj_name='DEPT1';
SQL_TEXT SQL_BIND RETURNCODE
------------------------------------------------------------ -------------------------------------------------- ----------
insert into dept1 values (:n,'a','b') #1(3):100 1438
insert into dept1 values (140,'a','b') 1438
--建立主键：
create unique index pk_dept1 on scott.dept1 (deptno);
alter table dept1 add constraint pk_dept1 primary key (deptno);
SCOTT@test> exec :n :=10;
PL/SQL procedure successfully completed.
SCOTT@test> insert into dept1 values (:n,'a','b');
insert into dept1 values (:n,'a','b')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT1) violated
SCOTT@test> select sql_text,sql_bind,returncode from dba_audit_trail where owner=user and obj_name='DEPT1';
SQL_TEXT SQL_BIND RETURNCODE
------------------------------------------------------------ -------------------------------------------------- ----------
insert into dept1 values (:n,'a','b') #1(2):10 1
insert into dept1 values (:n,'a','b') #1(3):100 1438
insert into dept1 values (140,'a','b') 1438
-- 很明显使用这种方式定位错误的记录效果更好.
-- 注意一个问题就是这种审计使用的是自治事务，即使你rollback，也会记录下来，要注意这些细节。
--补充字符串超出范围的例子：
SCOTT@test> variable c1 varchar2(20);
SCOTT@test> exec :c1 :='aaaaaaaaaaaaaaaaaaa' ;
PL/SQL procedure successfully completed.
SCOTT@test> insert into dept1 values (1,:c1,'b');
insert into dept1 values (1,:c1,'b')
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."DEPT1"."DNAME" (actual: 19, maximum: 14)
SCOTT@test> select sql_text,sql_bind,returncode from dba_audit_trail where owner=user and obj_name='DEPT1';
SQL_TEXT SQL_BIND RETURNCODE
------------------------------------------------------------ -------------------------------------------------- ----------
insert into dept1 values (1,:c1,'b') #1(19):aaaaaaaaaaaaaaaaaaa 12899