unlock a record of a table

[patnim17]

Hi,
One of the record in a table got locked somehow...
I wanted to know how can I release the lock on a row..
There must be a DBA command out there...

thanks
patnim17

 

[SantaMufasa]

Yes, and the DBA command to release the lock is:

ALTER SYSTEM KILL SESSION '<session_id>,<serial#>';

Where the <session_id> and <serial#> are those of the locking process. Here is a script that will give you the locking information:

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 "Unix|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

Thu Sep 01                                                                                                     page    1
                                                      Lock Listing

                                                    Machine name         Unix                   RBS
Locked Object        Oracle Username  O/S Username  of Locker            Process   SID/Ser#     Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
TEST.AUDITOR         TEST (INACTIVE)          dhunt       MyComputerName 2328:404  '8,2847'     RBS4 2005/09/01 11:13:38
TEST.S_EMP           TEST (INACTIVE)          dhunt       MyComputerName 2328:404  '8,2847'     RBS4 2005/09/01 11:13:38
**************************************************************************************************************************

Notice the "SID/Ser#" column has single quotes already embedded in the results for ease of copy-and-paste into the "ALTER SYSTEM KILL SESSION..." command.

Let us know if this information is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.