Find SQL currently executing in a session: https://oraclespin.wordpress.com/2010/05/09/how-to-find-sql-running-for-a-usersid/ Session running waiting on SYS@TESTDB> SELECT event, state, p1, p2, p3, seconds_in_wait FROM v$session_wait where sid = 247; EVENT STATE P1 P2 P3 SECONDS_IN_WAIT ---------------------------------------------------------------- --------------- ---- ---------- ---------- ---------- ------------------- db file scattered read WAITED KNOWN TIME 122 1188369 128 30 1 row selected. SYS@TESTDB> SELECT … Continue reading How to check/find session information (wait, io, lock, time)
Tag: v$lock
Find locks on a table
Using the step below one can find the locks on the table by looking for object id. Example: Window 1: SQL> update scott.test1 set x = 200; 16 rows updated. SQL> select userenv('sid') from dual; USERENV('SID') -------------- 19 Window 2: SQL> select object_id from dba_objects where object_name = 'TEST1' and owner = 'SCOTT'; OBJECT_ID ---------- … Continue reading Find locks on a table
Blocking locks
http://www.orafaq.com/node/854 SQL from the link above URL to find blocking SQL select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session … Continue reading Blocking locks
alexlima.com 