We run the following script every 20 minutes to identify the pending transactions ( to
be committed) and notify the appropriate application group (online or batch ) to take
action in consultation with the DBA group.
We filter this by username since we have some convention for batch programs and
Seems to me you should just have your program try to lock tables in
exclusive mode. If it succeeds, then rollback. If it fails
(timeout), it opens another session while the 'lock table' is waiting,
and finds the blocker.
Otherwise, if you are only interested in sessions that are actually
Title: RE: Table Locks
Call me crazy if you wish. But I would take a process or system state dump and navigate the locking session's object hierarchy. Yes, I know, ugly as Sin and potentially life-shortening.
HTH
Tony Aponte
-Original Message-
From: Alan Davey [mailto:[EMAIL
Thanks Rachel.
I spent the train ride reading the chapters on Instance Tuning and Dynamic Performance
Views hoping to find something, but no such luck. I learned a lot of other useful
things though, so it wasn't a waste of time.
Jacques, v$locked_object shows the table, but I already knew
I created following two views for developer's use and so far there have been
no complaints ..
CREATE OR REPLACE FORCE VIEW SYSTEM.DB$LOCKED_OBJECTS
(OBJECT_NAME, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, SQL_ACTIONS,
LOCK_MODE) AS
SELECT DO.object_name, lo.SESSION_ID, lo.oracle_username,
Thanks Raj.
I'll give these a try.
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 8/30/2002 10:08 AM, Jamadagni, Rajendra [EMAIL PROTECTED] wrote:
I created following two views for developer's use and so far there
have been
no complaints ..
CREATE OR REPLACE FORCE VIEW
Alan Davey wrote:
Thanks Rachel.
I spent the train ride reading the chapters on Instance Tuning and Dynamic
Performance Views hoping to find something, but no such luck. I learned a lot of
other useful things though, so it wasn't a waste of time.
Jacques, v$locked_object shows the
Title: RE: Table Locks
-Original Message-
From: Alan Davey [mailto:[EMAIL PROTECTED]]
Jacques, v$locked_object shows the table, but I already knew
which table was locked. I was hoping to find the offending
SQL statement.
Sorry, I misunderstood. I thought you were trying
Stephane you are more than welcome to disagree with me. Of course, when
I tried to find the sql, I was on version 7.3 so autonomous
transactions were unavailable.
--- Stephane Faroult [EMAIL PROTECTED] wrote:
Alan Davey wrote:
Thanks Rachel.
I spent the train ride reading the
I'm not sure it's possible to find the locking SQL and SID once the
session issues other SQL statements.
I spent a lot of time a few years back attempting to find it, without
success. I got the people at both Platinum Technology and Savant (yes,
I'm showing my age here) to try to find it as
Title: RE: Table Locks
Does v$locked_object show anything for those tables?
-Original Message-
From: Alan Davey [mailto:[EMAIL PROTECTED]]
I've noticed some locks on various tables and I'm trying to
figure out which DML statements are causing the locks. In
this example
What is the PCTFREE setting for the tables? Hope it is not 0.
Here is the quote form one of my article which explains this behavior
--BEGIN QUOTE
Each datablock will have an Interested Transaction List (ITL) that holds the
transaction id of that block during the
Sounds like you have run into the much annoying problem of missing
foreign key indexes. I would check that out first I think.
-Original Message-
Mascranghe
Sent: Tuesday, February 12, 2002 4:33 AM
To: Multiple recipients of list ORACLE-L
Hi all
We are running on 8.0.5.2.1 database.
If they are using DBMS_ALERT then since it uses
dbms_lock, it could be possible that the locking
problem is one of the application's making (not the
database). I can't remember the specifics, but things
like a long gap between signalling the alert and the
subsequent commit rings a bell as a
14 matches
Mail list logo
