Find running SQLs and Kill Session

==========find running SQLs
col USERNAME for a20
col SQL_TEXT for a100
col SID for 999999
set lines 300

select sesion.sid,
sesion.serial#,
sesion.username,
sesion.sql_id,
sesion.sql_child_number,
optimizer_mode,
hash_value,
address,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;


==========find locking sessions

SELECT vh.sid locking_sid,
 vs.status status,
 vs.program program_holding,
 vw.sid waiter_sid,
 vsw.program program_waiting
FROM v$lock vh,
 v$lock vw,
 v$session vs,
 v$session vsw
WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
 FROM v$lock
 WHERE request = 0
 INTERSECT
 SELECT id1, id2
 FROM v$lock
 WHERE lmode = 0)
 AND vh.id1 = vw.id1
 AND vh.id2 = vw.id2
 AND vh.request = 0
 AND vw.lmode = 0
 AND vh.sid = vs.sid
 AND vw.sid = vsw.sid;




 SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL;

 

==========Find Long SQLsselect v$session_longops.time_remaining, v$session_longops.opname, v$session_longops.start_time,
v$session_longops.last_update_time, v$session_longops.elapsed_seconds, ((v$session_longops.elapsed_seconds /
(v$session_longops.elapsed_seconds + v$session_longops.time_remaining))*100) pct_complete,
V$SQL.SQL_TEXT from v$session_longops, V$SQL where v$session_longops.SQL_ADDRESS=V$SQL.ADDRESS
and v$session_longops.time_remaining <> 0
order by V$SQL.SQL_TEXT, v$session_longops.last_update_time;


==========Kill
alter system kill session '929,23760' IMMEDIATE;
 
==========find pid
select     s.username
,     s.sid
,     s.serial#
,     p.spid
,     last_call_et
,     status
from      V$SESSION s
,     V$PROCESS p
where     s.PADDR = p.ADDR
and     s.sid= 929




==========another way to find sql

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,   
       MACHINE, 
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, 
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT 
  FROM V$SESSION SES,   
       V$SQLtext_with_newlines SQL 
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS 
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE 
   and Ses.AUDSID <> userenv('SESSIONID') 
 order by runt desc, 1,sql.piece;

  

Finding a locking session

How to identify lockers

This article will explain about locks on rows and on objects in ORACLE.

Locks on rows can cause performance problems or even impede a transaction from finishing, when there are processes running for long time we need to validate that they are not waiting on a row(s).

When there is a lock on a row there is also a lock on the dependent objects, if we want to perform a DDL on a locked object we will get an ORA-00054 error.

Scenario 1:

Terminal A is locking a row and Terminal B is waiting on it:

–TERMINAL A

1 2 3 4 5 6

SQL> update map1 set col2='MYLOCK' where col1=300;   1 row updated.   SQL> (..no commit here..)

–TERMINAL B

1 2	SQL> update map1 set col2='NEWVAL2' where col1=300; (..waiting..)

Now, lets create a session as a DBA User to monitor the system, this query will tell the locking and waiting SIDs.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SELECT vh.sid locking_sid,  vs.status status,  vs.program program_holding,  vw.sid waiter_sid,  vsw.program program_waiting FROM v$lock vh,  v$lock vw,  v$session vs,  v$session vsw WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  FROM v$lock  WHERE request = 0  INTERSECT  SELECT id1, id2  FROM v$lock  WHERE lmode = 0)  AND vh.id1 = vw.id1  AND vh.id2 = vw.id2  AND vh.request = 0  AND vw.lmode = 0  AND vh.sid = vs.sid  AND vw.sid = vsw.sid;

LOCKING_SID STATUS   PROGRAM_HOLDING                WAITER_SID PROGRAM_WAITING
----------- -------- ------------------------------ ---------- ------------------------------
 144        ACTIVE   sqlplus@rh4_node1.fadeserver.n        131 sqlplus@rh4_node1.fadeserver.n
                     et (TNS V1-V3)                            et (TNS V1-V3)

Here is an expanded version of the same query, it also includes jobs information.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

SELECT vs.username,  vs.osuser,  vh.sid locking_sid,  vs.status status,  vs.module module,  vs.program program_holding,  jrh.job_name,  vsw.username,  vsw.osuser,  vw.sid waiter_sid,  vsw.program program_waiting,  jrw.job_name,  'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# || ''';'  "Kill_Command" FROM v$lock vh,  v$lock vw,  v$session vs,  v$session vsw,  dba_scheduler_running_jobs jrh,  dba_scheduler_running_jobs jrw WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  FROM v$lock  WHERE request = 0  INTERSECT  SELECT id1, id2  FROM v$lock  WHERE lmode = 0)  AND vh.id1 = vw.id1  AND vh.id2 = vw.id2  AND vh.request = 0  AND vw.lmode = 0  AND vh.sid = vs.sid  AND vw.sid = vsw.sid  AND vh.sid = jrh.session_id(+)  AND vw.sid = jrw.session_id(+);

USERNAME OSUSER  LOCKING_SID STATUS   MODULE  PROGRAM_HO JOB_N USERNAME OSUSER  WAITER_SID PROGRAM_WA JOB_N Kill_
-------- ------- ----------- -------- ------- ---------- ----- -------- ------- ---------- ---------- ----- -----
CACOSTA  oracle          144 ACTIVE   SQL*Plu sqlplus@rh       CACOSTA  oracle         131 sqlplus@rh       alter
                                      s       4_node1.fa                                   4_node1.fa        syst
                                              deserver.n                                   deserver.n       em ki
                                              et (TNS V1                                   et (TNS V1       ll se
                                              -V3)                                         -V3)             ssion
                                                                                                            '144
                                                                                                            ,3897
                                                                                                            3';

We can see that the user CACOSTA, sid 144 is locking the session 131.

Scenario 2:
We are performing a DDL (alter somehow the object) and we get an ORA-00054 error.

I have canceled the waiting session in the example above and now I’m creating an index on the table:

1 2 3 4 5

SQL> create index ind2 on map1(col2); create index ind2 on map1(col2)  * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified

If I re-run the query fromt he previous scenario it won’t return any rows, because there are no waiting sessions (I canceled the waiting update).

First we need to find out the object ID:

1 2 3 4 5 6 7

SQL> select object_id from dba_objects  2  where owner='CACOSTA'  3  and object_name='MAP1';    OBJECT_ID ----------  52255

Now lets see who is blocking the object 52255

1 2 3 4 5 6 7 8 9 10 11

SELECT c.owner,  c.object_name,  c.object_type,  b.sid,  b.serial#,  b.status,  b.osuser,  b.machine FROM v$locked_object a, v$session b, dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id and a.object_id=52255;

OWNER    OBJECT_NAME   OBJECT_TYPE                SID    SERIAL# STATUS   OSUSER  MACHINE
-------- ------------- ------------------- ---------- ---------- -------- ------- ---------------
CACOSTA  MAP1          TABLE                      144      38973 ACTIVE   oracle  rh4_node1.fades
                                                                                  erver.net

Good luck!