Re: How to know the rows locked in a table ?

Vous utilisez quelle version d'Oracle?

Essayez catblock.sql, puis utllockt.sql dans $ORACLE_HOME/rdbms/admin. utllockt.sql va vous donner qq chose comme suit:

WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2
8 NONE None None 0 0
9 TX Share (S) Exclusive (X) 604 302 7 RW Exclusive (X) S/Row-X(SSX) 50304040 19 10 RW Exclusive (X) S/Row-X(SSX) 50304040 19

Une autre vue que vous pouvez essayer:
select holding_session, mode_held, waiting_session, mode_requested from dba_waiters
order by holding_session;

En organisant les ensembles d'apr�s holding_session, vous allez voir qui bloque les autres le plus.

Version 8:
V$ACCESS
This view displays objects in the database that are currently locked and the sessions that are accessing them.

V$LOCK
This view lists the locks currently held by the Oracle Server and outstanding requests for a lock or latch.

V$LOCKED_OBJECT
This view lists all locks acquired by every transaction on the system.

V$ENQUEUE_LOCK
This view displays all locks owned by enqueue state objects. The columns in this view are identical to the columns in V$LOCK.

Aussi, assurez-vous d'avoir un index sur chaque colonne FK -- sinon Oracle va barrer des rang�es du tableau pendant qu'il v�rifie s'il y a des rang�es d�pendantes, avant d'�liminer des donn�es.

Je ne sais pas comment trouver le ROWID, d'habitude je me soucie de trouver quels "locks" existent, leur origine, et puis je coupe la session si c'est n�cessaire.

De toute fa�on, le ROWID ne sera probablement pas souvent le m�me, tout d�pend du SQL �mit par les utilisateurs, non?

Sinc�rement,

Patrice Boivin
Halifax, Nouvelle-Ecosse
Canada.

Mathieu KREMBEL wrote:

> Hi,
>
> I need to know the locked rows of a table.
> In our intranet application, we use only one oracle user account.
> There are several sessions of the same user.
> The rows of a table can be locked with a "select... for update nowait"
> query. When the application fails, we have to delete the locks.
>
> The v$session view gives us the sid and the serial no,
> the sys.v$locked_object the object_id of the locked tables.
>
> But is it possible to get the rowid of the rows locked
> in the object_id tables ?
>
> Thanks for your answers,
>
> ===========================================
> Matthieu KREMBEL
> E-Mail : m.krembel_at_wanadoo.fr
Received on Mon Mar 06 2000 - 03:57:20 CET