Re: Enqueue Diagnosis

You have a couple of ways of putting a SELECT statement into a PL/SQL block (trigger, stored proc, anonymous, etc). First, you can create a cursor. Second, if your SELECT statement will return one row, then you can use something similar to the following:

DECLARE
   todays_date DATE;
BEGIN
   SELECT sysdate INTO todays_data FROM dual; END;
/

Furthermore, if you want to output the results, you can write to a file with the UTL_FILE package.

HTH,
Brian

Matt wrote:
>
> Brian Peasland <dba_at_remove_spam.peasland.com> wrote in message news:<3F3A3B4F.A37413BA_at_remove_spam.peasland.com>...
> > Query V$LOCK. You will see one SID that has a TX lock and the REQUEST is
> > mode 6. For that session, note the two values in ID1 and ID2. Now find
> > another session that has the same values and LMODE is non-zero. That is
> > the session that is holding the lock. Want to know which object? For
> > those same sessions, you should see a TM lock. The ID1 column of the TM
> > lock is the object id. With the object id, you can query DBA_OBJECTS to
> > find out which table it is. Further more, once you know who has the
> > lock, you can join V$SESSION and V$SQL for that SID to see what SQL
> > statements they are running.
> >
> > HTH,
> > Brian
>
> Good input... This info will give what I want to diagnose which rows
> are being contended for, but I only want to query v$lock, v$sessiom
> and v$sql when a large enqueue wait occurs. Therefore I need to be
> able to trigger the select on these table from my plsql script which
> checks for enqueue waits.
>
> How can I call the select statement from plsql. I need to be able to
> spool the output to a file because I cant monitor enqueue waits
> interactively.
>
> Matt

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"