am i wrong
A reader, June 22, 2008 - 3:34 pm UTC
does this query gets row level locking please correct me if i am wrong?
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 dba_OBJECTS,
(SELECT /*+ no_merge(a) no_merge(b) */
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#;
June 22, 2008 - 9:44 pm UTC
if you are asking "does this query, which uses hints in a nonsense fashion, show me all of the rows a session has locked"
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50507 your use of the no_merge hint is useless. But it looks cool. Hints are that way - makes it look cool.
the answer is "no" - there is no way to see that.
why don't you try this:
ask a question
rather than post an answer to an unknown question that may or may not even answer that question.