RE: Table Locks
We run the following script every 20 minutes to identify the pending transactions ( to be committed) and notify the appropriate application group (online or batch ) to take action in consultation with the DBA group. We filter this by username since we have some convention for batch programs and online programs; We set thresholds for minutes_pending 5 minutes for onlines and 60 for batch. select sysdate, '1' inst_id, sid, serial#, username, substr(terminal,1,10) termi nal, osuser, t.start_time, r.name, t.used_ublk ROLLB BLKS, decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status) )) status, round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS') ) *24*60 ,0 ) minutes_pending from v$transaction t, v$rollname r, v$session s where t.xidusn = r.usn and t.ses_addr = s.saddr order by t.start_time; Hope this helps. Govind -Original Message- Sent: Saturday, November 30, 2002 12:39 AM To: Multiple recipients of list ORACLE-L Seems to me you should just have your program try to lock tables in exclusive mode. If it succeeds, then rollback. If it fails (timeout), it opens another session while the 'lock table' is waiting, and finds the blocker. Otherwise, if you are only interested in sessions that are actually blocking other sessions, just look in v$lock where block = 1. As interesting as it seems, I think you won't succeed in trying to put triggers on x$kgllk or anything like that. They're not real tables - just table-like accessors for memory structures in the SGA. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Fri, 29 Nov 2002, [EMAIL PROTECTED] wrote: I would like to send an alert message to a client when a data row is locked for more than a certain period of time. For this can I write triggers on the system tables. If so on which table should I write a trigger to retrieve the table lock information. Are there any implications on writing triggers on the system tables. The alert message should be sent automatically in the sense, can I write an alert and signal it from a trigger written on some system table where the lock information is available? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Table Locks
Seems to me you should just have your program try to lock tables in exclusive mode. If it succeeds, then rollback. If it fails (timeout), it opens another session while the 'lock table' is waiting, and finds the blocker. Otherwise, if you are only interested in sessions that are actually blocking other sessions, just look in v$lock where block = 1. As interesting as it seems, I think you won't succeed in trying to put triggers on x$kgllk or anything like that. They're not real tables - just table-like accessors for memory structures in the SGA. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Fri, 29 Nov 2002, [EMAIL PROTECTED] wrote: I would like to send an alert message to a client when a data row is locked for more than a certain period of time. For this can I write triggers on the system tables. If so on which table should I write a trigger to retrieve the table lock information. Are there any implications on writing triggers on the system tables. The alert message should be sent automatically in the sense, can I write an alert and signal it from a trigger written on some system table where the lock information is available? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Table Locks
Title: RE: Table Locks Call me crazy if you wish. But I would take a process or system state dump and navigate the locking session's object hierarchy. Yes, I know, ugly as Sin and potentially life-shortening. HTH Tony Aponte -Original Message- From: Alan Davey [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 29, 2002 4:44 PM To: Multiple recipients of list ORACLE-L Subject: Table Locks Hi All, I've noticed some locks on various tables and I'm trying to figure out which DML statements are causing the locks. In this example, the lock isn't being released because the developer forgot to include a commit/rollback. If I look at v$session which is causing the lock and query v$sqlarea with the values in sql_address and prev_sql_addr, I only see select statements that were issued after the DML (in this case a delete). I can query v$sqlarea with the locked table name and find the delete statement, but how do I link this back to the sid that issued it? Also, what if there had been multiple DML statements by this user, how would I know which was the first/last one executed? I'm RTFMing, but so far no luck. Any help would be greatly appreciated. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey 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).
Re: Table Locks
Thanks Rachel. I spent the train ride reading the chapters on Instance Tuning and Dynamic Performance Views hoping to find something, but no such luck. I learned a lot of other useful things though, so it wasn't a waste of time. Jacques, v$locked_object shows the table, but I already knew which table was locked. I was hoping to find the offending SQL statement. Have a great weekend everyone. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 8/29/2002 10:43 PM, Rachel Carmichael [EMAIL PROTECTED] wrote: I'm not sure it's possible to find the locking SQL and SID once the session issues other SQL statements. I spent a lot of time a few years back attempting to find it, without success. I got the people at both Platinum Technology and Savant (yes, I'm showing my age here) to try to find it as well, figuring their technical people were better at this sort of thing than I am... no luck. I don't think Oracle stores the statement and who issued it, just the rollback info necessary and the fact that there is a lock. --- Alan Davey [EMAIL PROTECTED] wrote: Hi All, I've noticed some locks on various tables and I'm trying to figure out which DML statements are causing the locks. In this example, the lock isn't being released because the developer forgot to include a commit/rollback. If I look at v$session which is causing the lock and query v$sqlarea with the values in sql_address and prev_sql_addr, I only see select statements that were issued after the DML (in this case a delete). I can query v$sqlarea with the locked table name and find the delete statement, but how do I link this back to the sid that issued it? Also, what if there had been multiple DML statements by this user, how would I know which was the first/last one executed? I'm RTFMing, but so far no luck. Any help would be greatly appreciated. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Alan Davey 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).
RE: Table Locks
I created following two views for developer's use and so far there have been
no complaints ..
CREATE OR REPLACE FORCE VIEW SYSTEM.DB$LOCKED_OBJECTS
(OBJECT_NAME, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, SQL_ACTIONS,
LOCK_MODE) AS
SELECT DO.object_name, lo.SESSION_ID, lo.oracle_username, lo.OS_USER_NAME,
DECODE(locked_mode,
1, 'SELECT',
2, 'SELECT FOR UPDATE / LOCK ROW SHARE',
3, 'INSERT/UPDATE/DELETE/LOCK ROW EXCLUSIVE',
4, 'CREATE INDEX/LOCK SHARE',
5, 'LOCK SHARE ROW EXCLUSIVE',
6, 'ALTER TABLE/DROP TABLE/DROP INDEX/TRUNCATE TABLE/LOCK
EXCLUSIVE') sql_actions,
DECODE(locked_mode, 1, 'NULL', 2, 'SS - SUB SHARE', 3, 'SX - SUB
EXCLUSIVE',
4, 'S - SHARE', 5, 'SSX - SHARE/SUB EXCLUSIVE', 6, 'X -
EXCLUSIVE') Lock_mode
FROM sys.V_$LOCKED_OBJECT lo, DB$OBJECTS DO
WHERE DO.object_id = lo.object_id;
CREATE PUBLIC SYNONYM DB$LOCKED_OBJECTS FOR SYSTEM.DB$LOCKED_OBJECTS;
GRANT SELECT ON SYSTEM.DB$LOCKED_OBJECTS TO PUBLIC;
and
CREATE OR REPLACE FORCE VIEW SYSTEM.DB$LOCKS
(OBJ_OWNER, OBJ_NAME, OBJ_TYPE, OBJ_ROWID, DB_USER,
SID, LOCK_TYPE, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) AS
SELECT owner obj_owner,
object_name obj_name,
object_type obj_type,
dbms_rowid.rowid_create(1, row_wait_obj#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) obj_rowid,
a.username db_user, a.sid sid, a.TYPE lock_type,
a.row_wait_file#, a.row_wait_block#, a.row_wait_row#
FROM DB$OBJECTS,
(SELECT a.username, a.sid, a.row_wait_obj#, a.ROW_WAIT_FILE#,
a.ROW_WAIT_BLOCK#, a.ROW_WAIT_ROW#, b.TYPE
FROM sys.V_$SESSION a, sys.V_$LOCK b
WHERE a.username IS NOT NULL
AND a.row_wait_obj# -1
AND a.sid = b.sid
AND b.TYPE IN ('TX','TM')
) a
WHERE object_id = a.row_wait_obj#;
CREATE PUBLIC SYNONYM DB$LOCKS FOR SYSTEM.DB$LOCKS;
GRANT SELECT ON SYSTEM.DB$LOCKS TO PUBLIC;
DB$OBJECTs is a snapshot of DBA_OBJECTS, it is too slow to select from
DBA_OBJECTS, so I created a snapshot that is refreshed on a daily basis, it
works fine for me.
Hope this helps some. As others have mentioned, currently locked rows are
very difficult to find, what you can find though is the rowid for which a
lock is requested.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
Sent: Friday, August 30, 2002 9:48 AM
To: Multiple recipients of list ORACLE-L
Thanks Rachel.
I spent the train ride reading the chapters on Instance Tuning and Dynamic
Performance Views hoping to find something, but no such luck. I learned a
lot of other useful things though, so it wasn't a waste of time.
Jacques, v$locked_object shows the table, but I already knew which table was
locked. I was hoping to find the offending SQL statement.
Have a great weekend everyone.
Regards,
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 8/29/2002 10:43 PM, Rachel Carmichael [EMAIL PROTECTED] wrote:
I'm not sure it's possible to find the locking SQL and SID once the
session issues other SQL statements.
I spent a lot of time a few years back attempting to find it, without
success. I got the people at both Platinum Technology and Savant
(yes,
I'm showing my age here) to try to find it as well, figuring their
technical people were better at this sort of thing than I am... no
luck.
I don't think Oracle stores the statement and who issued it, just
the
rollback info necessary and the fact that there is a lock.
--- Alan Davey [EMAIL PROTECTED] wrote:
Hi All,
I've noticed some locks on various tables and I'm trying to figure
out which DML statements are causing the locks. In this example,
the
lock isn't being released because the developer forgot to include
a
commit/rollback.
If I look at v$session which is causing the lock and query v$sqlarea
with the values in sql_address and prev_sql_addr, I only see select
statements that were issued after the DML (in this case a delete).
I
can query
v$sqlarea with the locked table name and find the delete statement,
but how do I link this back to the sid that issued it? Also, what
if
there had been multiple DML statements by this user, how would
I know
which was the first/last one executed?
I'm RTFMing, but so far no luck. Any help would be greatly
appreciated.
Regards,
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alan Davey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing
Re: Table Locks
Thanks Raj.
I'll give these a try.
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 8/30/2002 10:08 AM, Jamadagni, Rajendra [EMAIL PROTECTED] wrote:
I created following two views for developer's use and so far there
have been
no complaints ..
CREATE OR REPLACE FORCE VIEW SYSTEM.DB$LOCKED_OBJECTS
(OBJECT_NAME, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, SQL_ACTIONS,
LOCK_MODE) AS
SELECT DO.object_name, lo.SESSION_ID, lo.oracle_username, lo.OS_USER_NAME,
DECODE(locked_mode,
1, 'SELECT',
2, 'SELECT FOR UPDATE / LOCK ROW SHARE',
3, 'INSERT/UPDATE/DELETE/LOCK ROW EXCLUSIVE',
4, 'CREATE INDEX/LOCK SHARE',
5, 'LOCK SHARE ROW EXCLUSIVE',
6, 'ALTER TABLE/DROP TABLE/DROP INDEX/TRUNCATE TABLE/LOCK
EXCLUSIVE') sql_actions,
DECODE(locked_mode, 1, 'NULL', 2, 'SS - SUB SHARE', 3, 'SX
- SUB
EXCLUSIVE',
4, 'S - SHARE', 5, 'SSX - SHARE/SUB EXCLUSIVE', 6,
'X -
EXCLUSIVE') Lock_mode
FROM sys.V_$LOCKED_OBJECT lo, DB$OBJECTS DO
WHERE DO.object_id = lo.object_id;
CREATE PUBLIC SYNONYM DB$LOCKED_OBJECTS FOR SYSTEM.DB$LOCKED_OBJECTS;
GRANT SELECT ON SYSTEM.DB$LOCKED_OBJECTS TO PUBLIC;
and
CREATE OR REPLACE FORCE VIEW SYSTEM.DB$LOCKS
(OBJ_OWNER, OBJ_NAME, OBJ_TYPE, OBJ_ROWID, DB_USER,
SID, LOCK_TYPE, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
AS
SELECT owner obj_owner,
object_name obj_name,
object_type obj_type,
dbms_rowid.rowid_create(1, row_wait_obj#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) obj_rowid,
a.username db_user, a.sid sid, a.TYPE lock_type,
a.row_wait_file#, a.row_wait_block#, a.row_wait_row#
FROM DB$OBJECTS,
(SELECT a.username, a.sid, a.row_wait_obj#, a.ROW_WAIT_FILE#,
a.ROW_WAIT_BLOCK#, a.ROW_WAIT_ROW#, b.TYPE
FROM sys.V_$SESSION a, sys.V_$LOCK b
WHERE a.username IS NOT NULL
AND a.row_wait_obj# -1
AND a.sid = b.sid
AND b.TYPE IN ('TX','TM')
) a
WHERE object_id = a.row_wait_obj#;
CREATE PUBLIC SYNONYM DB$LOCKS FOR SYSTEM.DB$LOCKS;
GRANT SELECT ON SYSTEM.DB$LOCKS TO PUBLIC;
DB$OBJECTs is a snapshot of DBA_OBJECTS, it is too slow to select
from
DBA_OBJECTS, so I created a snapshot that is refreshed on a daily
basis, it
works fine for me.
Hope this helps some. As others have mentioned, currently locked
rows are
very difficult to find, what you can find though is the rowid for
which a
lock is requested.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of
ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
Sent: Friday, August 30, 2002 9:48 AM
To: Multiple recipients of list ORACLE-L
Thanks Rachel.
I spent the train ride reading the chapters on Instance Tuning and
Dynamic
Performance Views hoping to find something, but no such luck. I
learned a
lot of other useful things though, so it wasn't a waste of time.
Jacques, v$locked_object shows the table, but I already knew which
table was
locked. I was hoping to find the offending SQL statement.
Have a great weekend everyone.
Regards,
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 8/29/2002 10:43 PM, Rachel Carmichael [EMAIL PROTECTED]
wrote:
I'm not sure it's possible to find the locking SQL and SID once
the
session issues other SQL statements.
I spent a lot of time a few years back attempting to find it, without
success. I got the people at both Platinum Technology and Savant
(yes,
I'm showing my age here) to try to find it as well, figuring their
technical people were better at this sort of thing than I am...
no
luck.
I don't think Oracle stores the statement and who issued it, just
the
rollback info necessary and the fact that there is a lock.
--- Alan Davey [EMAIL PROTECTED] wrote:
Hi All,
I've noticed some locks on various tables and I'm trying to figure
out which DML statements are causing the locks. In this example,
the
lock isn't being released because the developer forgot to include
a
commit/rollback.
If I look at v$session which is causing the lock and query v$sqlarea
with the values in sql_address and prev_sql_addr, I only see
select
statements that were issued after the DML (in this case a delete).
I
can query
v$sqlarea with the locked table name and find the delete statement,
but how do I link this back to the sid that issued it? Also,
what
if
there had been multiple DML statements by this user, how would
I know
which was the first/last one executed?
I'm RTFMing, but so far no luck. Any help would be greatly
appreciated.
Regards,
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Re: Table Locks
Alan Davey wrote: Thanks Rachel. I spent the train ride reading the chapters on Instance Tuning and Dynamic Performance Views hoping to find something, but no such luck. I learned a lot of other useful things though, so it wasn't a waste of time. Jacques, v$locked_object shows the table, but I already knew which table was locked. I was hoping to find the offending SQL statement. Have a great weekend everyone. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 8/29/2002 10:43 PM, Rachel Carmichael [EMAIL PROTECTED] wrote: I'm not sure it's possible to find the locking SQL and SID once the session issues other SQL statements. I spent a lot of time a few years back attempting to find it, without success. I got the people at both Platinum Technology and Savant (yes, I'm showing my age here) to try to find it as well, figuring their technical people were better at this sort of thing than I am... no luck. I don't think Oracle stores the statement and who issued it, just the rollback info necessary and the fact that there is a lock. --- Alan Davey [EMAIL PROTECTED] wrote: Hi All, I've noticed some locks on various tables and I'm trying to figure out which DML statements are causing the locks. In this example, the lock isn't being released because the developer forgot to include a commit/rollback. If I look at v$session which is causing the lock and query v$sqlarea with the values in sql_address and prev_sql_addr, I only see select statements that were issued after the DML (in this case a delete). I can query v$sqlarea with the locked table name and find the delete statement, but how do I link this back to the sid that issued it? Also, what if there had been multiple DML statements by this user, how would I know which was the first/last one executed? I'm RTFMing, but so far no luck. Any help would be greatly appreciated. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 I hate to disagree even partially with Rachel, but IMHO if a lock exists, then a cursor *may* still be open somewhere (I insist on 'may' because this is typically untrue with SQL*Plus). In that case, V$OPEN_CURSOR provides the SID and the hash value/address allowing to join with V$SQL_TEXT. There is, also, V$SQL_CURSORS. Unfortunately, this one is, I think, 'private' to a session and making use of CURNO requires a plunge into the X$ tables. If I may suggest something, it is to create an 'after' trigger on the locked table which systematically logs (in an autonomous transaction) the statement which fired it. If I do not err, when a lock occurs then the last logged statement should be the offending one. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
RE: Table Locks
Title: RE: Table Locks -Original Message- From: Alan Davey [mailto:[EMAIL PROTECTED]] Jacques, v$locked_object shows the table, but I already knew which table was locked. I was hoping to find the offending SQL statement. Sorry, I misunderstood. I thought you were trying to find the session.
Re: Table Locks
Stephane you are more than welcome to disagree with me. Of course, when I tried to find the sql, I was on version 7.3 so autonomous transactions were unavailable. --- Stephane Faroult [EMAIL PROTECTED] wrote: Alan Davey wrote: Thanks Rachel. I spent the train ride reading the chapters on Instance Tuning and Dynamic Performance Views hoping to find something, but no such luck. I learned a lot of other useful things though, so it wasn't a waste of time. Jacques, v$locked_object shows the table, but I already knew which table was locked. I was hoping to find the offending SQL statement. Have a great weekend everyone. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 8/29/2002 10:43 PM, Rachel Carmichael [EMAIL PROTECTED] wrote: I'm not sure it's possible to find the locking SQL and SID once the session issues other SQL statements. I spent a lot of time a few years back attempting to find it, without success. I got the people at both Platinum Technology and Savant (yes, I'm showing my age here) to try to find it as well, figuring their technical people were better at this sort of thing than I am... no luck. I don't think Oracle stores the statement and who issued it, just the rollback info necessary and the fact that there is a lock. --- Alan Davey [EMAIL PROTECTED] wrote: Hi All, I've noticed some locks on various tables and I'm trying to figure out which DML statements are causing the locks. In this example, the lock isn't being released because the developer forgot to include a commit/rollback. If I look at v$session which is causing the lock and query v$sqlarea with the values in sql_address and prev_sql_addr, I only see select statements that were issued after the DML (in this case a delete). I can query v$sqlarea with the locked table name and find the delete statement, but how do I link this back to the sid that issued it? Also, what if there had been multiple DML statements by this user, how would I know which was the first/last one executed? I'm RTFMing, but so far no luck. Any help would be greatly appreciated. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 I hate to disagree even partially with Rachel, but IMHO if a lock exists, then a cursor *may* still be open somewhere (I insist on 'may' because this is typically untrue with SQL*Plus). In that case, V$OPEN_CURSOR provides the SID and the hash value/address allowing to join with V$SQL_TEXT. There is, also, V$SQL_CURSORS. Unfortunately, this one is, I think, 'private' to a session and making use of CURNO requires a plunge into the X$ tables. If I may suggest something, it is to create an 'after' trigger on the locked table which systematically logs (in an autonomous transaction) the statement which fired it. If I do not err, when a lock occurs then the last logged statement should be the offending one. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
Re: Table Locks
I'm not sure it's possible to find the locking SQL and SID once the session issues other SQL statements. I spent a lot of time a few years back attempting to find it, without success. I got the people at both Platinum Technology and Savant (yes, I'm showing my age here) to try to find it as well, figuring their technical people were better at this sort of thing than I am... no luck. I don't think Oracle stores the statement and who issued it, just the rollback info necessary and the fact that there is a lock. --- Alan Davey [EMAIL PROTECTED] wrote: Hi All, I've noticed some locks on various tables and I'm trying to figure out which DML statements are causing the locks. In this example, the lock isn't being released because the developer forgot to include a commit/rollback. If I look at v$session which is causing the lock and query v$sqlarea with the values in sql_address and prev_sql_addr, I only see select statements that were issued after the DML (in this case a delete). I can query v$sqlarea with the locked table name and find the delete statement, but how do I link this back to the sid that issued it? Also, what if there had been multiple DML statements by this user, how would I know which was the first/last one executed? I'm RTFMing, but so far no luck. Any help would be greatly appreciated. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
RE: Table Locks
Title: RE: Table Locks Does v$locked_object show anything for those tables? -Original Message- From: Alan Davey [mailto:[EMAIL PROTECTED]] I've noticed some locks on various tables and I'm trying to figure out which DML statements are causing the locks. In this example, the lock isn't being released because the developer forgot to include a commit/rollback. If I look at v$session which is causing the lock and query v$sqlarea with the values in sql_address and prev_sql_addr, I only see select statements that were issued after the DML (in this case a delete). I can query v$sqlarea with the locked table name and find the delete statement, but how do I link this back to the sid that issued it? Also, what if there had been multiple DML statements by this user, how would I know which was the first/last one executed? I'm RTFMing, but so far no luck. Any help would be greatly appreciated.
RE: Table Locks
What is the PCTFREE setting for the tables? Hope it is not 0. Here is the quote form one of my article which explains this behavior --BEGIN QUOTE Each datablock will have an Interested Transaction List (ITL) that holds the transaction id of that block during the life cycle of the transaction modifying that datablock. A transaction, which modifies a record in the datablock, must get an ITL slot in that datablock. The number of ITL slots in a datablock is defined by the INITRANS (which defaults 1 for data blocks and 2 for index blocks) and MAXTRANS. While formatting a new block Oracle creates the transaction slots specified by INITRANS parameter. MAXTRANS specifies maximum number of ITLs created for a datablock and it defaults to 255. In practice you dont need more MAXTRANS unless your AVG_ROW_LENGTH is very small and the segment is frequently updated. The creation of additional Interested Transaction Lists (ITL) slots is subject to free space in the datablock because each ITL takes approximately 24 bytes of free space in the variable header of that datablock. Initial space reserved by INITRANS cannot be reused for data insertion. But if a datablock is fully packed due to less PCTFREE or PCTFREE=0 and when two transactions are accessing the same block, one has to wait till the transaction commits (or rollbacks). Here row level locks are escalated in to block level locks. ---END QUOTE--- And I don't see any reason for row locks becoming table locks unless you have an un indexed foreign key. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Mascranghe Sent: Tuesday, February 12, 2002 4:33 AM To: Multiple recipients of list ORACLE-L Hi all We are running on 8.0.5.2.1 database. Once we had a database creash and was restored. After that the users have been experiencing locks. What happens is when one user locks some rows, other users are also getting stuck. But they are not locking the same rows. We are looking at the code to see whether any unusual things are there. One of the programs in the system uses DBMS_ALERT. How can we find more information - what rows are being locked, and any other relevant info about locks? Is there any ways in which a row locks turns out to be a table lock ? Thanks Alroy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alroy Mascranghe 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). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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).
RE: Table Locks
Sounds like you have run into the much annoying problem of missing foreign key indexes. I would check that out first I think. -Original Message- Mascranghe Sent: Tuesday, February 12, 2002 4:33 AM To: Multiple recipients of list ORACLE-L Hi all We are running on 8.0.5.2.1 database. Once we had a database creash and was restored. After that the users have been experiencing locks. What happens is when one user locks some rows, other users are also getting stuck. But they are not locking the same rows. We are looking at the code to see whether any unusual things are there. One of the programs in the system uses DBMS_ALERT. How can we find more information - what rows are being locked, and any other relevant info about locks? Is there any ways in which a row locks turns out to be a table lock ? Thanks Alroy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alroy Mascranghe 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: Kimberly Smith 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).
RE: Table Locks
If they are using DBMS_ALERT then since it uses dbms_lock, it could be possible that the locking problem is one of the application's making (not the database). I can't remember the specifics, but things like a long gap between signalling the alert and the subsequent commit rings a bell as a cause of locking problems... hth connor --- K Gopalakrishnan [EMAIL PROTECTED] wrote: What is the PCTFREE setting for the tables? Hope it is not 0. Here is the quote form one of my article which explains this behavior --BEGIN QUOTE Each datablock will have an Interested Transaction List (ITL) that holds the transaction id of that block during the life cycle of the transaction modifying that datablock. A transaction, which modifies a record in the datablock, must get an ITL slot in that datablock. The number of ITL slots in a datablock is defined by the INITRANS (which defaults 1 for data blocks and 2 for index blocks) and MAXTRANS. While formatting a new block Oracle creates the transaction slots specified by INITRANS parameter. MAXTRANS specifies maximum number of ITLs created for a datablock and it defaults to 255. In practice you dont need more MAXTRANS unless your AVG_ROW_LENGTH is very small and the segment is frequently updated. The creation of additional Interested Transaction Lists (ITL) slots is subject to free space in the datablock because each ITL takes approximately 24 bytes of free space in the variable header of that datablock. Initial space reserved by INITRANS cannot be reused for data insertion. But if a datablock is fully packed due to less PCTFREE or PCTFREE=0 and when two transactions are accessing the same block, one has to wait till the transaction commits (or rollbacks). Here row level locks are escalated in to block level locks. ---END QUOTE--- And I don't see any reason for row locks becoming table locks unless you have an un indexed foreign key. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Mascranghe Sent: Tuesday, February 12, 2002 4:33 AM To: Multiple recipients of list ORACLE-L Hi all We are running on 8.0.5.2.1 database. Once we had a database creash and was restored. After that the users have been experiencing locks. What happens is when one user locks some rows, other users are also getting stuck. But they are not locking the same rows. We are looking at the code to see whether any unusual things are there. One of the programs in the system uses DBMS_ALERT. How can we find more information - what rows are being locked, and any other relevant info about locks? Is there any ways in which a row locks turns out to be a table lock ? Thanks Alroy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alroy Mascranghe 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). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
