RE: sql statement "hanging" and unable to query v$lock - Oracle 8

Hi,

The reply below was assuming the problem is a lock.

I'd check v$session_event to see / confirm what is really being waited = upon.

See Steve Adam's useful session wait script at http://www.ixora.com.au/scripts/waits.htm

Regards,
Bruce

-----Original Message-----
From: Reardon, Bruce (CALBBAY)=20
Sent: Thursday, 11 January 2001 10:05

Hi,

Try querying dba_blockers and dba_waiters - these might give the sid of = the
blocking session.
If these views don't exist then you can create them with the = catblock.sql
script found in rdms/admin directory.

Also, if you have OEM, try using the lock monitor tool.

Alternatively, if it doesn't show up here, look in dba_lock_internal. = The
following is the query I use - this will sometimes show up blocks that = don't
show up in dba_locks.

select * from dba_lock_internal
where=20

       ( mode_held =3D 'Null' OR mode_held =3D 'None' )    AND ( mode_requested <> 'None' )
;

• and can then do something like the following

 select * from dba_lock_internal
 where
   mode_held <> 'Null'
   and mode_held <> 'None'
   and lock_id1 =3D 'as appropriate- based on what came back from the = first
query'
 ;

Hope this helps.

Bruce Reardon
mailto:bruce.reardon_at_comalco.riotinto.com.au

-----Original Message-----
Sent: Thursday, 11 January 2001 9:41
To: Multiple recipients of list ORACLE-L 8.0.5

Question here. I have a query that's taking an abnormally long time to execute (at least in my opinion). I can see the query that's executing =
(I

assume this is the one since I'm in a development database with only = very
few connections, and there's only one with loaded_versions =3D 1, open_versions =3D 1, and users_executing =3D 1 in v$sql). I figured that there was probably a table being locked somewhere. But = when I
try to query v$lock (I typed in "select * from v$lock" in SQL*Plus on = the
server) that query hangs too! At least it never returns back to the = prompt.
However I can still connect to the database in a new session and query = other
v$ views such as v$session.=20
How do I find out why the process is hanging? I don't see any ORA- = errors in
the alert log.=20
Oracle Enterprise Edition 8.0.5 on Sun Solaris 5.7=20 Jacques R. Kilcho=EBr=20
(949) 754-8816=20

Quest Software, Inc.=20
8001 Irvine Center Drive=20 Received on Wed Jan 10 2001 - 17:33:25 CST