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
>> 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).
*********************************************************************2
This e-mail message is confidential, intended only for the named recipient(s) above
and may contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank you.
*********************************************************************2
