OraFAQ Forum: Server Administration » Monitoring and logging Locks.

Home » RDBMS Server » Server Administration » Monitoring and logging Locks.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Monitoring and logging Locks. [message #151108]

Tue, 13 December 2005 03:49

sussudio
Messages: 30
Registered: March 2005
Location: London

Member

I need a way of detecting and logging all locks made on database tables in a certian shema. I'm not sure it is possible but my boss wants it!!! needless to say he is not technical. Anyway. I wondered if anybody could tell me a way of logging all locks made against tables in a database. I need to know.

Table Name.
The row identifier of the row locked.
Date and time the row was locked.

many thanks for any help you can give.

Report message to a moderator

Re: Monitoring and logging Locks. [message #151127 is a reply to message #151108]

Tue, 13 December 2005 06:37

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Monitor locks. Yes.
Logging those information?. You have to design your own custom method. May be dbms_job will help you. schedule a query and log the output into a table.
Google yourself for 'Monitor oracle locks' or anything like that.
You will get a zillion hits.
You can get as fancy as you want.
Here is One such and probably one of the best.
But you need to understand more.
http://www.jlcomp.demon.co.uk/faq/row_locker.html

scott@9i > delete from emp;

14 rows deleted.

scott@9i > !mon_lock mutation

                      OS              OS      OS         OS      Database                  Oracle  Lock                           Mode            Mode
   SID WAITER BLOCKER Program         Usernam Machine    Process Object                    Usernam Type                           Held            Requested
------ ------ ------- --------------- ------- ---------- ------- ------------------------- ------- ------------------------------ --------------- ----------
     9                sqlplus@mutatio oracle  mutation.w 6040    SCOTT.EMP                 SCOTT   TM                             Row-X (SX)      None
     9                sqlplus@mutatio oracle  mutation.w 6040    SCOTT.                    SCOTT   TX                             Exclusive       None


scott@9i > commit;

Commit complete.

scott@9i > !mon_lock mutation

no rows selected

Report message to a moderator

Re: Monitoring and logging Locks. [message #151156 is a reply to message #151127]

Tue, 13 December 2005 08:00

Frank
Messages: 7901
Registered: March 2000

Senior Member

There is no way you will get an identifier to which row is locked. Oracle doesn't store that information in a table. See this question for an explanation.
(there is another explanation two responses further down in the given link)

hth

[Updated on: Tue, 13 December 2005 08:02]

Report message to a moderator

Previous Topic:	Oracle Client needs help
Next Topic:	Question on Partitions.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 15:40:52 CDT 2024