ITPub博客

首页 > 数据库 > Oracle > [20140529]使用审计记录一些不能DML的记录

[20140529]使用审计记录一些不能DML的记录

原创 Oracle 作者:lfree 时间:2014-05-29 09:53:22 0 删除 编辑

[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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1172214/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2701
  • 访问量
    6491865