Here are some lock detection scripts for Oracle in a non-RAC environment. Next time, we will post the ones that apply to the RAC environment and will post the series of steps on how to help detect those issues using the gv$ views. In the following SQL scripts, it is assumed that you already have the select privileges on the objects in the from clause. If you do not, then you need to grant those select privileges to the Oracle User using which you will be running these scripts. If you want to look for a specific Oracle user, replace the “username is not null” clause with the equality operator and given that particular user name. Also, the RULE hint is used because we ran into a performance issue in 9iR2 where accessing the system views was very slow using the ALL_ROWS or FIRST_ROWS_N optimizer modes. The RULE mode is deprecated in 10g, so you can either leave it there if you want a generic script for all environments (when used that way, the hint is ignored) or just yank it out for your 10g installations.
Login into database using SQLPlus to run the following 2 sqls to find out the locking information in the
database.
SQL 1: Shows the SID and SERIAL# of all the locks currently held in the db along with the type of the lock
and related information.
SQL 2: Shows the SID and SERIAL# of all the locks along with the currently SQL being run.
set lines 120 pages 1000 feed on verify off echo off
column sid heading ‘Sid’ format 99999
column serial_num heading ‘Serial#’ format 999999
column db_username heading ‘DB|Username’ format a15 wrap
column process heading ‘Process’ format a10 truncate
column host_pid heading ‘Host|PID’ format a8 wrap
column machine heading ‘Host’ format a12 truncate
column program heading ‘Program’ format a25 truncate
column object_name heading ‘Object|Name’ format a20 wrap
column lock_type heading ‘Lock|Type’ format a5 truncate
column mode_held heading ‘Mode|Held’ format a10 truncate
column mode_req heading ‘Mode|Requested’ format a15 truncate
column ctime heading ‘Time Hrs.|Since|LMode|Held’ format a8 truncate
column is_blocking heading ‘Blocking?’ format a12 wrap
break on sid on serial_num on process on db_username on spid on machine
select /*+rule */
ses.sid as sid,
ses.serial# as serial_num,
ses.process as process,
ses.username as db_username,
pro.spid as host_pid,
ses.machine as machine,
substr(ses.program,1,30) as program,
substr(obj.object_name,1,20) as object_name,
loc.lock_type as lock_type,
loc.mode_held as mode_held,
loc.mode_requested as mode_req,
to_char(trunc(sysdate) + loc.last_convert/(24*3600), ‘HH24:MI:SS’) as ctime,
loc.blocking_others as is_blocking
from v$session ses,
v$process pro,
dba_lock loc,
dba_objects obj
where ses.sid = loc.session_id
and ses.paddr = pro.addr
and loc.lock_id1 = obj.object_id
and ses.username is not null
order by ses.sid, ses.serial#, ses.process, ses.username
/
clear columns
clear breaks
prompt
prompt
col sid heading ‘Session|Id’ format 99999
col serial_num heading ‘Serial#’ format 999999
column db_username heading ‘DB|Username’ format a15 wrap
column lock_id1 format a8 noprint
column ctime heading ‘Time Hrs.|Since|LMode|Held’ format a8 truncate
col sql heading ‘SQL|Text’ format a64 wrap
break on sid on serial_num on process on db_username
select /*+rule */
a.sid as sid,
a.serial# as serial_num,
a.process as process,
a.username as db_username,
c.lock_id1 as lock_id1,
to_char(trunc(sysdate) + c.last_convert/(24*3600), ‘HH24:MI:SS’) as ctime,
b.sql_text sql
from v$session a,
v$sqltext b,
dba_lock c
where a.sid = c.session_id
and a.username is not null
and ((a.sql_address = b.address
and a.sql_hash_value = b.hash_value)
or (a.prev_sql_addr = b.address
and a.prev_hash_value = b.hash_value))
order by a.sid, a.serial#, a.process, a.username, c.lock_id1, b.piece
/
clear breaks
clear columns
If your Oracle user has the “ALTER SYSTEM” privileges, you can then use the SID and SERIAL# information from above and use that to kill the sessions (if at all needed) that are holding blocking locks for a longer duration of time.
