SQL> create table ljw (id number,name varchar(20));
操作已执行
已用时间: 18.868(毫秒). 执行号:303.
SQL> insert into ljw values(1,'test');
影响行数 1
已用时间: 0.595(毫秒). 执行号:304.
SQL> commit;
操作已执行
已用时间: 5.179(毫秒). 执行号:305.
会话1:
SQL> update ljw set name='ljw' where id=1;
影响行数 1
已用时间: 0.908(毫秒). 执行号:306.
会话2:
SQL> update ljw set name='mc' where id=1;
会话3:
查看所有会话信息:
SQL> select USER_NAME,sess_id,TRX_ID,SQL_TEXT,RUN_STATUS from v$sessions;
行号 USER_NAME SESS_ID TRX_ID SQL_TEXT RUN_STATUS
---------- --------- -------------------- -------------------- -------------------------------------------------------------------- ----------
1 LJW 140265444676904 2820 update ljw set name='ljw' where id=1; IDLE
2 LJW 140265713112056 2821 update ljw set name='mc' where id=1; RUNNING
3 LJW 140265780220920 2823 select USER_NAME,sess_id,TRX_ID,SQL_TEXT,RUN_STATUS from v$sessions; RUNNING
查看相关锁信息:
SQL> select * from v$lock;
行号 ADDR TRX_ID LTYPE LMODE BLOCKED TABLE_ID ROW_IDX
---------- -------------------- -------------------- ------ ----- ----------- ----------- --------------------
1 140267294386096 2820 OBJECT IX 0 1291 0
2 140267294386176 2821 OBJECT IX 0 1291 0
3 140267294517168 2823 TID X 0 0 2823
4 140267294517248 2821 TID X 0 0 2821
5 140267294517328 2820 TID X 0 0 2820
6 140267294517408 2821 TID X 1 0 2820
查看相关事务等待信息:
SQL> select id,status,sess_id,waiting from v$trx where status='LOCK WAIT';
行号 ID STATUS SESS_ID WAITING
---------- -------------------- --------- -------------------- --------------------
1 2821 LOCK WAIT 140265713112056 140267294384296
总结:
查询出事务锁的会话源头信息:
select s.USER_NAME,s.sess_id,s.TRX_ID,s.SQL_TEXT,s.RUN_STATUS from v$sessions s,v$lock l where s.trx_id=l.row_idx and l.blocked=1;
行号 USER_NAME SESS_ID TRX_ID SQL_TEXT RUN_STATUS
---------- --------- -------------------- -------------------- ------------------------------------- ----------
1 LJW 140265444676904 2820 update ljw set name='ljw' where id=1; IDLE
被阻索会话信息:
select s.USER_NAME,s.sess_id,s.TRX_ID,s.SQL_TEXT,s.RUN_STATUS from v$sessions s,v$lock l where s.trx_id=l.trx_id and l.blocked=1;
行号 USER_NAME SESS_ID TRX_ID SQL_TEXT RUN_STATUS
---------- --------- -------------------- -------------------- ------------------------------------ ----------
1 LJW 140265713112056 2821 update ljw set name='mc' where id=1; RUNNING
批量查杀源头会话:
select 'SP_CLOSE_SESSION(' ||s.sess_id||')' from v$sessions s,v$lock l where s.trx_id=l.row_idx and l.blocked=1;
行号 'SP_CLOSE_SESSION('||S.SESS_ID||')'
---------- -----------------------------------
1 SP_CLOSE_SESSION(140265444676904)
批量查杀被阻塞会话:
select 'SP_CLOSE_SESSION(' ||s.sess_id||')' from v$sessions s,v$lock l where s.trx_id=l.trx_id and l.blocked=1;
行号 'SP_CLOSE_SESSION('||S.SESS_ID||')'
---------- -----------------------------------
1 SP_CLOSE_SESSION(140265713112056)