How to rowlock on a table 3

[zircon06]

Can you tell procedure to remove rowlock on tables

Thanks in advance

 

[SantaMufasa]

Zircon,

There are multiple methods to remove a row lock on a table (proceding from least painful to most painful):

1) Have user cancel her/his transaction via ROLLBACK command.
2) Have user save her/his work via COMMIT command.
3) Have user log off the server gracefully (causing an implied COMMIT to occur).
4) Have user ungracefully cancel her/his session (e.g., client machine shutdown/power off), causing an implied ROLLBACK to occur.
5) As a DBA, kill the user's session (causing an implied ROLLBACK to occur).
6) Shutdown the database instance.

I presume you are asking about Option 5, "Kill the user's session". To do that, you must be a DBA, you must identify the locking user's Session IDentifier (SID) and session's Serial Number. An easy way to accomplish this is to use my script that follows:

ttitle "Lock Listing"
set linesize 150
set echo off
col oruser format a16 heading "Oracle Username"
col osuser format a13 heading "O/S Username"
col obj format a20 heading "Locked Object"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a19
col rs heading "RBS|Name" format a4
col unix heading "O/S|Process" format a9
col computer heading "Machine name|of Locker" format a20
set linesize 120
select     owner||'.'||object_name obj
   ,oracle_username||' ('||s.status||')' oruser
   ,os_user_name osuser
   ,machine computer
   ,l.process unix
   ,''''||s.sid||','||s.serial#||'''' ss
   ,r.name rs
   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from       v$locked_object l
   ,dba_objects o
   ,v$session s
   ,v$transaction t
   ,v$rollname r
where l.object_id = o.object_id
  and s.sid=l.session_id
  and s.taddr=t.addr
  and t.xidusn=r.usn
order by osuser, ss, obj
/
ttitle off
set linesize 132

When you run the script (which I call "locks.sql", you receive the following output (which I have abbreviated horizontally to avoid the confusing line wrap. The important thing is that the output shows the "SID/Serial#" combination that you need to manually cancell the session, thus releasing the locks that the session possesses:

SQL> @locks
...                               Machine name         Unix                   
...Oracle Username  O/S Username  of Locker            Process   SID/Ser#...
...---------------- ------------- -------------------- --------- --------...
...TEST (ACTIVE)    TENFOLD\dhunt TENFOLD\TFMOBILE5331 1016:1380 '8,1787'...
******************************************************************************

With the SID/Serial# combination of the locking session, you can kill the session with the following command:

SQL> alter system kill session '8,1787';

System altered.

Let us know if this answers your questions satisfactorily.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

Excellent answer, to the point and useful.

 

[zircon06]

Thanks santa for excellent answer