> Dear all,
>
> suppose I used a SELECT ... for update nowait statement to
lock a row in a table. And, may be
> there are some problems with my programs that the lock is
left on the server. How can I
> release the lock by logging in through another session?
>
> thanks!
>
> --
> Cheers,
> Kenneth (Milk Ken)
>
>
> ___\/ __ _____ ___ _____ The Chinese
University
> //////`\\\\ _( \/ )/ )__ of
> |;;;;/~\\\| ( Touch me at : ) Hong
Kong
> (|($)-($)|) ( )
> | '. | _/ cwyue_at_se.cuhk.edu.hk ) Department of
Sys. Eng. &
> \ O / ~~~\ kenneth.yue_at_alumni.ust.hk) Eng.
Mgmt.
> ___/|\=/|\___ ( keyue_at_asl.com.hk ___)
> / | \\___// | \ ( ___)
> | |* === | (_____)\____/\_____) Tutor of
SEG6460
>
>
>
There is no way I know of to directly release the lock. however
you can check if the process that created the lock is still
active and kill that. once the process that owns the lock is
dead oracle should clear the lock shortly. here is a snippet of
code that will display all of the processes owned by a user
from a sqlplus session:
accept myUser char prompt "Enter the username : "
column username format a15
column program format a20
column SID format a5
column SERIAL# format a10
select v$session.username,v$session.terminal,osuser,
status,v$session.program,v$process.program, v$process.spid,
to_char(v$session.sid) "SID", to_char(v$session.serial#)
"SERIAL#"
from v$session,v$process
where addr=paddr and type='USER'
and v$session.username = upper('&myUser')
order by v$session.terminal;
the above will list all of the process owned by a specific user
(the owner of the lock). once you have this information you can
kill an individual process by using the
alter system kill session 'SID,SER#'
where the SID is the SID and SER# is the SERIAL# returned by
the previous query. note that the sid and ser# must be enclosed
in single quotes.
once the process is dead the lock should soon be released.
if the process is not listed you can use the following script
to list all of the dml locks in the database and find out the
owner.
column SESSION_D format 999999
select v.Username, v.osuser, d.* from sys.dba_dml_locks d,
v$session v
where v.SID = d.SESSION_ID
order by v.UserName
this will give you the actual owner of the lock and then you
cat re-run the first script (or modify the above to return the
sid & ser#) and kill the session.
andre azaroff
aazaroff_at_redrose.net
--
This answer is courtesy of QuestionExchange.com
http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=6473&cus_id=USENET&qtn_id=4040
Received on Wed Oct 27 1999 - 21:10:07 CDT
