Sean,
A lock would normally go away when the database is shut down, but if the
problem was in the application (or the users), the lock would probably come
right back again.
Check In v$lock for type TX locks with lmode=6 . These are exclusive row
locks caused by updates or select for update, inserts or deletes. If you see
any of these you're on the right path. (The column ctime shows how long the
lock was held in seconds)
The session thats reporting the error might not have any rows in v$lock, because
they probably did a select for update nowait. In otherwords they checked to see
if the row they wanted to update was locked and because it was, they returned
control to the interactive user instead of requesting a lock. You could check
the row_wait_obj# in v$session which will have the object id of the table that
the select for update was issued against. Sessions that didn't wait will have a
value of -1 , sessions that did wait will have the object_id in dba_objects.
Run-away sessions are less common now than they used to be. I guess when you
shut down the database, if you stopped the Oracle service (NT) or killed all the
oracle processes you would be sure that there aren't any of them around (unless
it was coming over a db link?)
Hope this is a bit of help to you.
Chaim
O'Neill, Sean [EMAIL PROTECTED] on 07/05/2001 11:55:55 AM
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:(bcc: Chaim Katz/Completions/Bombardier)
On of our applications is reporting a problem trying to access on a record
in a table. The application is reporting that a transaction is reserved for
update. The app in question hung whilst working on the record a few days
ago. I'm not convinced that this is an Oracle problem, i.e. a row lock of
some sort, as I've had a peek at DB level via v$lock and v_$locked_object,
v$access but can't seem to find any locks on the table in question. If it
is an Oracle lock of some type how can I identify it?. The database gets
shutdown every night for COLD backups but the app prog in question is
reporting problem for number of days now. Is it possible that some session
might still be haunting around?.
Would appreciate any advice/help on this one folks?.
Sean :)
Rookie Data Base Administrator
[0%] OCP Oracle8i DBA
[0%] OCP Oracle9i DBA
Organon (Ireland) Ltd.
E-mail: [EMAIL PROTECTED] [subscribed: Digest Mode]
Visit: http://groups.yahoo.com/group/Oracle-OCP-DBA
Nobody loves me but my mother... and she could be jivin' too. - BB King
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: O'Neill, Sean
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
