| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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
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
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 adavey_at_competitrack.com 212-604-0200 x106 On 8/29/2002 10:43 PM, Rachel Carmichael <wisernet100_at_yahoo.com> wrote:Received on Fri Aug 30 2002 - 09:08:41 CDT
>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 <adavey_at_competitrack.com> 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
>> adavey_at_competitrack.com
>> 212-604-0200 x106
>>
>>
>>
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> --
>> Author: Alan Davey
>> INET: adavey_at_competitrack.com
>>
>> 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
>
>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: ListGuru_at_fatcity.com (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: adavey_at_competitrack.com 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: ListGuru_at_fatcity.com (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: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.com 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: ListGuru_at_fatcity.com (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).
- text/plain attachment: ESPN_Disclaimer.txt
 |
 |