Printable View
Not very technical end-user come and complain that the record she requires is lock for a fair amount of time. Could you give me a systematic steps that you as a DBA expert will follow to find the root of the problem ( for example do you start to look at V$lock, V$session, OEM, I don't know what else ... )
or if you have a all-inclusive script for similar case. I am looking for fastest and systematic way.
The user could set a trace on the session as
eg: for ORA-1591
alter session set events '1591 trace name errorstack level 10';
and analyze the trace that had been created at $ORACLE_BASE/admin/SID/udump
To do this, the user should have the trace enabled.
Hope this would help you.
Sam
Can you cover more different situations. Systematic approach.
Normally when you set the trace to the error and the trace level to 10 the max trace, it would spit out the entire stack
One other query that you can use is
select sid,
sql_address,
terminal,
SUBSTR(OBJECT_NAME,1,20),
b.sql_text
FROM v$session,
v$sqlarea b ,
USER_OBJECTS
WHERE process in ( select distinct process from v$locked_object )
and sql_address = b.address AND USER_OBJECTS.OBJECT_ID IN ( SELECT OBJECT_ID FROM v$locked_object);
to find the object causing the dead lock.
This link under metalink would get your the scripts to monitor the locking
http://metalink.oracle.com/metalink/...nical_Articles
One other thing is that when deadlock occours oracle would create an ora-600 message and would create a trace file at udump for the corresponding sid. Check them for more detail.
Sam
Sam