happydba

工作中经常遇到锁的问题，但是具体holder到底做了那些操作比较困难，这里给出一个使用logmnr查找holder操作的过程
查找holder做过那些操作
SELECT decode(request,0,’holder:’,'waiter:’)||sid sid,id1,id2,lmode,request,type

FROM v$lock

WHERE (id1,id2,type) IN

(SELECT id1,id2,type

FROM v$lock

WHERE request>0)

ORDER BY id1,request;
SID ID1 ID2 LMODE REQUEST TYPE

holder:143 65542 1645 6 0 TX

waiter:139 65542 1645 0 6 TX

waiter:142 65542 1645 0 6 TX

waiter:144 65542 1645 0 6 TX
假设出现了上述锁，针对这种情况，做下列操作
SQL> select group#,thread#,status from v$Log;
GROUP# THREAD# STATUS

———- ———- —————-

1 1 CURRENT

2 1 INACTIVE

3 1 ACTIVE

SQL> alter system switch logfile;

SQL> select group#,thread#,status from v$Log;
GROUP# THREAD# STATUS

———- ———- —————-

1 1 ACTIVE

2 1 CURRENT

3 1 INACTIVE
SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination c:archstdchicago

Oldest online log sequence 318

Next log sequence to archive 320

Current log sequence 320

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => ‘C:archchicagoARC00319_0664534646.001′,OPTIONS => dbms_logmnr.NEW);

execute DBMS_LOGMNR.START_LOGMNR(options =>dbms_logmnr.dict_from_online_catalog);

create table system.test_logmnr as select * from v$logmnr_contents;

execute DBMS_LOGMNR.END_LOGMNR;
select * from system.test_logmnr t where t.session# =143;

SCN CSCN TIMESTAMP COMMIT_TIMESTAMP THREAD# LOG_ID XIDUSN XIDSLT XIDSQN PXIDUSN PXIDSLT PXIDSQN RBASQN RBABLK RBABYTE UBAFIL UBABLK UBAREC UBASQN ABS_FILE# REL_FILE# DATA_BLK# DATA_OBJ# DATA_OBJD# SEG_OWNER SEG_NAME TABLE_NAME SEG_TYPE SEG_TYPE_NAME TABLE_SPACE ROW_ID SESSION# SERIAL# USERNAME SESSION_INFO TX_NAME ROLLBACK OPERATION OPERATION_CODE SQL_REDO SQL_UNDO RS_ID SEQUENCE# SSN CSF INFO STATUS REDO_VALUE UNDO_VALUE SQL_COLUMN_TYPE SQL_COLUMN_NAME REDO_LENGTH REDO_OFFSET UNDO_LENGTH UNDO_OFFSET DATA_OBJV# SAFE_RESUME_SCN XID PXID AUDIT_SESSIONID

4198405 2009-1-7 17:33:52 1 319 1 6 1645 1 6 1645 319 1569 16 2 0 0 0 0 0 0 0 0 0 AAAAAAAAAAAAAAAAAA 143 55 TEST login_username=TEST client_info= OS_username=pepeli-cnpepeli Machine_name=CN-ORACLEpepeli-cn OS_terminal=pepeli-cn OS_process_id=4324:3100 OS_program_name=plsqldev.exe 0 START 6 set transaction read write; 0x00013f.00000621.0010 1 0 0 0 4396 4397 0 0 0 0 0 010006006D060000 010006006D060000 461199

4198405 2009-1-7 17:33:52 1 319 1 6 1645 1 6 1645 319 1569 16 2 0 0 0 2 4 20 52197 52197 TEST PERSON PERSON 2 TABLE USERS AAAMvlAAEAAAAAUAAA 143 55 TEST login_username=TEST client_info= OS_username=pepeli-cnpepeli Machine_name=CN-ORACLEpepeli-cn OS_terminal=pepeli-cn OS_process_id=4324:3100 OS_program_name=plsqldev.exe 0 UPDATE 3 update “TEST”.”PERSON” set “ID” = ’201′ where “ID” = ’1′ and “NAME” = ‘aa’ and ROWID = ‘AAAMvlAAEAAAAAUAAA’; update “TEST”.”PERSON” set “ID” = ’1′ where “ID” = ’201′ and “NAME” = ‘aa’ and ROWID = ‘AAAMvlAAEAAAAAUAAA’; 0x00013f.00000621.0010 1 0 0 0 4398 4399 0 0 0 0 1 010006006D060000 010006006D060000 461199