附上一些基础的查询sql

--查询事务锁信息，阻塞的时候可以查询
select * from V$LOCK L where l.blocked=1;

--查询session（state：CREATE创建、STARTUP启动、IDLE空闲、ACTIVE活动、WAIT等待、UNKNOWN未知）
select * from v$sessions ;

--查询事务等待信息
select * from v$trxwait;

--查询哪些事务处于等待状态
select * from v$trx where status ='LOCK WAIT';

--根据V$sessions 和v$lock 查询产生阻塞的事务会话
select s.sess_id,s.SQL_TEXT,s.RUN_STATUS from v$sessions s , v$lock l where l.tid=s.trx_Id and l.blocked=1;

--关闭session
--select 'SP_CLOSE_SESSION(' ||s.sess_id||')' from v$sessions s,v$lock l where s.trx_id=l.tid and l.blocked=1;
call sp_close_session(sess_id);

--查看当前库中是否存在阻塞
select * from v$dsc_trxwait;


--查询历史锁信息
select * from V$DEADLOCK_HISTORY;

--查询执行sql的历史
select * from V$SQL_HISTORY;

附上一些组合的查询sql

select b.object_name,
       c.sess_id,
       c.thrd_id,
       c.state,
       c.sql_text,
       a.*
  from v$lock a,
       dba_objects b,
       v$sessions c
 where a.table_id = b.object_id
   and a.ltype = 'OBJECT'
   and a.trx_id = c.trx_id
   and a.ign_flag=0;

select s.sess_id,
       s.SQL_TEXT,
       s.RUN_STATUS
  from v$sessions s ,
       v$lock l
 where l.tid=s.trx_Id
   and l.blocked=1;

select 'SP_CLOSE_SESSION(' ||s.sess_id||')'
  from v$sessions s,
       v$lock l
 where s.trx_id=l.tid
   and l.blocked=1;



SELECT SYSDATE STATTIME,
       DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS ,
       '被阻塞的信息' WT ,
       S1.SESS_ID WT_SESS_ID,
       S1.SQL_TEXT WT_SQL_TEXT,
       S1.STATE WT_STATE,
       S1.TRX_ID WT_TRX_ID,
       S1.USER_NAME WT_USER_NAME,
       S1.CLNT_IP WT_CLNT_IP,
       S1.APPNAME WT_APPNAME,
       S1.LAST_SEND_TIME WT_LAST_SEND_TIME,
       '引起阻塞的信息' EM,
       s2.SESS_ID EM_SESS_ID,
       S2.SQL_TEXT EM_SQL_TEXT,
       S2.STATE FM_STATE,
       S2.TRX_ID FM_TRX_ID,
       S2.USER_NAME FM_USER_NAME,
       S2.CLNT_IP FM_CLNT_IP,
       S2.APPNAME FM_APPNAME,
       S2.LAST_SEND_TIME FM_LAST_SEND_TIME
  FROM V$SESSIONS S1 , 
       V$SESSIONS S2, 
       V$TRXWAIT W
 where S1.TRX_ID=W.ID
   AND S2.TRX_ID=W.WAIT_FOR_ID;