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
>>> --
>>> 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).
>
--
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).
