日志挖掘，发现大量delete .... where ROWID = 'AAAnQyABBAAAAAOAAA'

最近有个系统经常出现客户端被hung住，没有反应的情况，因为是Oracle10g，
首先使用了hist视图，查找到大量的TX锁，
select b.username,
       a.SQL_ID,
       a.session_id,
       a.EVENT,
       a.WAIT_CLASS,
       a.WAIT_TIME,
       a.PROGRAM,
       a.client_id
  from dba_hist_active_sess_history a, dba_users b
 where a.user_id = b.user_id
  and a.SAMPLE_TIME between to_date('2011-05-03 02:24:00','yyyy-mm-dd hh24:mi:ss')
  and to_date('2011-05-203 05:03:00','yyyy-mm-dd hh24:mi:ss');

进而使用日志挖掘，发现有大量的
delete .... where  ROWID = 'AAAnQyABBAAAAAOAAA'
语句；

怀疑是主键重复导致的，所以自己做了个实验：
1.创建t1表
-- Create table
create table T1
(
  A NUMBER not null,
  B VARCHAR2(20)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T1
  add constraint PK_T1 primary key (A);

1.在一个session内对t1表执行插入操作
insert into t1 values(3,'monica2');
insert into t1 values(4,'monica2');
insert into t1 values(5,'monica2');
insert into t1 values(6,'monica2');

2.分别在其他4个session内执行上面4条语句

均不提交

3.查看系统锁的情况
select s.sql_id,w.EVENT,a.SQL_TEXT,s.USERNAME from v$session s,v$sqlarea a,v$session_wait w
where s.sql_id=a.SQL_ID
  and s.SID=w.SID
  and s.EVENT='enq: TX - row lock contention'
1    f7fr7w2jrjvz1    enq: TX - row lock contention    insert into t1 values(6,'monica2')     TEST
2    apq3p5ztr0zta    enq: TX - row lock contention    insert into t1 values(3,'monica2')     TEST
3    8ntkd93mb77sj    enq: TX - row lock contention    insert into t1 values(5,'monica2')     TEST
4    8ys4qq60d47f9    enq: TX - row lock contention    insert into t1 values(4,'monica2')     TEST

4.对session执行commit;
其他四个session均报主键冲突的错误
ORA-00001:unique constraint(TEST.PK_T1)violated

5.再次查看此时锁的情况，锁已经全部不见了

6.切换日志
7.进行日志挖掘
SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('redo03.log', SYS.DBMS_LOGMNR.NEW)

PL/SQL 过程已成功完成。
/dev/recomredo2m
SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

PL/SQL 过程已成功完成。

SQL> SELECT TO_CHAR(TIMESTAMP ,'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER ='TEST' AND TABLE_NAME = 'T1';

2011-05-03 17:33:36
insert into "TEST"."T1"("A","B") values ('3','monica2');

TIMESTAMP
-------------------
SQL_REDO
--------------------------------------------------------------------------------

2011-05-03 17:33:36
insert into "TEST"."T1"("A","B") values ('4','monica2');

2011-05-03 17:33:36
insert into "TEST"."T1"("A","B") values ('5','monica2');

2011-05-03 17:33:36
insert into "TEST"."T1"("A","B") values ('6','monica2');

TIMESTAMP
-------------------
SQL_REDO
--------------------------------------------------------------------------------

2011-05-03 17:33:39
insert into "TEST"."T1"("A","B") values ('3','monica2');

2011-05-03 17:33:39
insert into "TEST"."T1"("A","B") values ('4','monica2');

2011-05-03 17:33:42
insert into "TEST"."T1"("A","B") values ('5','monica2');

TIMESTAMP
-------------------
SQL_REDO
--------------------------------------------------------------------------------

2011-05-03 17:33:45
insert into "TEST"."T1"("A","B") values ('6','monica2');

2011-05-03 17:34:06
delete from "TEST"."T1" where ROWID = 'AAAnQyABBAAAAAOAAA';

2011-05-03 17:34:06
delete from "TEST"."T1" where ROWID = 'AAAnQyABBAAAAAMAAA';

TIMESTAMP
-------------------
SQL_REDO
--------------------------------------------------------------------------------

2011-05-03 17:34:06
delete from "TEST"."T1" where ROWID = 'AAAnQyABBAAAAAQAAE';

2011-05-03 17:34:06
delete from "TEST"."T1" where ROWID = 'AAAnQyABBAAAAAPAAA';


到此，验证结束。

说明：Oracle在做插入的时候，均把数据插入到了表中，在提交后，插入失败的数据数据库将自动删除。
所有操作均在日志中记录。

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

转载于:http://blog.itpub.net/7353848/viewspace-694352/