RE: select only user causing locks?

From: <JayMiller_at_TDWaterhouse.com>
Date: Tue, 10 May 2005 10:59:57 -0400
Message-ID: <5B257A26B4845C469B87871B6CEFE507015EADBD@usnjc04wmx003.tdwaterhouse.com>

Is it possible to have a session trigger on a lock table or select for update statement? If so you could set it to email you should the situation recur.

BTW, another type of lock that can come about "innocently" with the SELECT privilege is a TX (rollback segment) lock. If you select across a database link the TX lock will be held until a COMMIT or ROLLBACK is issued or the session is ended (even though no DML was issued).

This should be just an academic point since any power user sitting at a SQL prompt that issues a "select for update" or "lock table" command will be shot. Unfortunately the power users have moved on to Toad and it is pretty easy to unwittingly have Toad do a "select for update".

Given that a query (audit) account is a requirement and that I can't control what tool they will use, anyone have ideas on how to stop the locks?

Thanks,
Steven
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Tue May 10 2005 - 11:08:57 CDT