Saturday, September 27, 2008

All Open Queries Under Oracle

Interested in seeing all open connections/queries in an Oracle server?
select *
from
(select * from v$lock ) l,
(select * from v$session) s,
(select sql_fulltext, sql_id from v$sql) f
where l.sid(+)=s.sid
AND f.sql_id=s.prev_sql_id(+)
AND SCHEMANAME='PROQ'
AND MACHINE='custeng100.la1.cj.com'


And, this one shows just the locked/deadlocked queries:
select SESSION_ID, BLOCKING_SESSION, ORACLE_USERNAME, OS_USER_NAME, OBJECT_NAME, MACHINE, SECONDS_IN_WAIT
from v$locked_object, dba_objects, v$session
where v$locked_object.object_id=dba_objects.object_id
AND v$session.sid = v$locked_object.session_id

No comments:

Post a Comment

AddThis

Bookmark and Share