SELECT /*+ rule*/
B.INST_ID,
A.EVENT,
C.SID,
B.SPID,
C.MACHINE,
C.SECONDS_IN_WAIT,
'kill -9 ' || B.SPID,
'alter system kill session '''||C.SID||','||C.SERIAL#||''';',
E.OBJECT_NAME,
dbms_rowid.ROWID_CREATE(1,C.ROW_WAIT_OBJ#,C.ROW_WAIT_FILE#,C.ROW_WAIT_BLOCK#,C.ROW_WAIT_ROW#) RWID,
D.SQL_TEXT
FROM GV$SESSION_WAIT A,
GV$PROCESS B,
GV$SESSION C,
GV$SQLAREA D,
DBA_OBJECTS E
WHERE A.EVENT = 'enq: TX - row lock contention'
AND A.SID = C.SID
AND C.PADDR = B.ADDR
AND A.INST_ID = C.INST_ID
AND A.INST_ID = B.INST_ID
AND A.INST_ID = D.INST_ID
AND C.SQL_ID = D.SQL_ID(+)
AND C.ROW_WAIT_OBJ# = E.OBJECT_ID
-- AND A.INST_ID = 2
ORDER BY INST_ID, SECONDS_IN_WAIT DESC;
select s.SID,s.MACHINE,s.USERNAME,s.STATUS,s.LOGON_TIME,s2.SQL_TEXT from v$session s,v$sqlarea s2
where s.SQL_ID = s2.SQL_ID
order by s.LOGON_TIME desc
查询未释放连接另一种方法:
1.利用oracle数据库的动态性能视图v$open_cursor,即通过游标来查找未释放的数据库连接
select * from v$open_cursor where user_name='SCOTT'; 其中SCOTT为登录的用户名
如果java数据库的连接没有关闭通过上面的sql语句就可以看到被查询的sql语句 select * from dept
在被打开的游标里面出现多次。如下所示:
123 3997DA58 315 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
120 39976A20 309 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
124 3996F9E8 303 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
然后根据sql语句去查找,写java代码是那个sql语句没有关闭。
B.INST_ID,
A.EVENT,
C.SID,
B.SPID,
C.MACHINE,
C.SECONDS_IN_WAIT,
'kill -9 ' || B.SPID,
'alter system kill session '''||C.SID||','||C.SERIAL#||''';',
E.OBJECT_NAME,
dbms_rowid.ROWID_CREATE(1,C.ROW_WAIT_OBJ#,C.ROW_WAIT_FILE#,C.ROW_WAIT_BLOCK#,C.ROW_WAIT_ROW#) RWID,
D.SQL_TEXT
FROM GV$SESSION_WAIT A,
GV$PROCESS B,
GV$SESSION C,
GV$SQLAREA D,
DBA_OBJECTS E
WHERE A.EVENT = 'enq: TX - row lock contention'
AND A.SID = C.SID
AND C.PADDR = B.ADDR
AND A.INST_ID = C.INST_ID
AND A.INST_ID = B.INST_ID
AND A.INST_ID = D.INST_ID
AND C.SQL_ID = D.SQL_ID(+)
AND C.ROW_WAIT_OBJ# = E.OBJECT_ID
-- AND A.INST_ID = 2
ORDER BY INST_ID, SECONDS_IN_WAIT DESC;
select s.SID,s.MACHINE,s.USERNAME,s.STATUS,s.LOGON_TIME,s2.SQL_TEXT from v$session s,v$sqlarea s2
where s.SQL_ID = s2.SQL_ID
order by s.LOGON_TIME desc
查询未释放连接另一种方法:
1.利用oracle数据库的动态性能视图v$open_cursor,即通过游标来查找未释放的数据库连接
select * from v$open_cursor where user_name='SCOTT'; 其中SCOTT为登录的用户名
如果java数据库的连接没有关闭通过上面的sql语句就可以看到被查询的sql语句 select * from dept
在被打开的游标里面出现多次。如下所示:
123 3997DA58 315 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
120 39976A20 309 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
124 3996F9E8 303 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
然后根据sql语句去查找,写java代码是那个sql语句没有关闭。