Q

Way to identify which row is being locked

In Oracle 9i, is there a way to identify the specific row being locked in a particular table? I have seen much code that identifies the object being locked and by whom, but I need to be able to identify the row.

This Content Component encountered an error
In Oracle 9i, is there a way to identify the specific row being locked in a particular table? I have seen much code that identifies the object being locked and by whom, but I need to be able to identify the row.
Oracle has several views for showing lock status:

  • DBA_BLOCKERS -- Shows non-waiting sessions holding locks being waited on
  • DBA_DDL_LOCKS -- Shows all DDL locks held or being requested
  • DBA_DML_LOCKS -- Shows all DML locks held or being requested
  • DBA_LOCK_INTERNAL -- Displays one row for every lock or latch held or being requested
  • DBA_LOCKS -- Shows all locks or latches held or being requested
  • DBA_WAITERS -- Shows all sessions waiting on, but not holding waited for locks

The DBA_LOCK_INTERNAL view is best to show locks for a specific user, and you can specify the query in the form:

SELECT
   NVL(b.username,'SYS') username,
   session_id,lock_type,mode_held,
   mode_requested,lock_id1,lock_id2
FROM
   sys.dba_lock_internal a,
   sys.v_$session b
where  . . .
For a complete list of over 600 Oracle scripts, I would recommend the Ault Oracle script location at www.oracle-script.com.
This was first published in November 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close