 |
 |
 |
 |
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
|
|
 |
|
|
|
 |
|
 |
Find locks with Oracle
Donald K. Burleson |
The Oracle data dictionary views can be quite complex and difficult to understand. Hence, many Oracle professionals have to be able to use pre-written scripts to allow us to be able to quickly identify important conditions with the Oracle database.
The following script is commonly used by Oracle professionals who need to quickly find out all database objects that are locked within their system. As we may know, Oracle sets locks in order to manage concurrent updates and ensure that the database maintains its internal integrity.
Most Oracle professionals use of the v$locked_object view in order to gather information about objects that are locked within the Oracle database. The v$locked_object view can also be joined into the v$session view in order to gather session level information (SID, PID, status, machine) , and also joined into the dba_objects view in order to get the owner, the object name, and the type of objects that is currently being locked within the database.
The following script can be used in order quickly identify all lock objects within your Oracle system.
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
Here is a sample execution of this script and we can see that SID number 11 is holding a lock on the FND_CONCURRENT_REQUESTS table.
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE SID SERIAL# STATUS OSUSER
------------------ ---------- ---------- -------- ------------------------------
MACHINE
----------------------------------------------------------------
APPLSYS
FND_CONCURRENT_REQUESTS
TABLE 11 29 INACTIVE applmgr
corp-hp1
If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress. It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:
http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm
|
|
