hello oracle DBAs,
We are using oracle 9i rel 2 on solaris 8.
one of the table is seldom getting locked in exclusive mode..
Actually we are applying a delete statement on that table from a java program(application server is tomcat5.0).
And the program just hangs there..when we open oracle enterprise manager console--sessions..we could see an exclusive lock on that table.when we killed the session from there then its working fine..
We could not figure out coz. it happens seldom..and to our knowledge exclusive locks are applied explicitly using lock table command.
could anybody help me in this regard..

..sridhar

While an exclusive lock may exist, it may not necessarily be the direct culprit.
The "hung" session may be in enqueue wait.
===============================================
select object_name, do.object_id, session_id, serial#, osuser, username, locked_mode , start_time, module
from dba_objects do, v$session, v$locked_object lo, v$transaction
where to_date(start_time,'MM/DD/YY HH24:MIS') < (sysdate-(1/1440))
and locked_mode in (3,5,6)
and session_id = sid
and saddr = ses_addr
and lo.object_id = do.object_id
order by start_time desc
/
=============================================
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/
================================================== ======
One of hese two queries might provide more visibility into the situation.

thanks anacedent,
Actually when we try view the details in enterprise manager console..its showing this table in exclusive lock..and that comes only this is excecuted from java program.
And we executed the queries given by you.. but says no rows selected...
we come to know that..we need to give commit after every 'x' number of rows..like 10..100..So..we tried the following procedure.. but when we execute it.. it hangs there..
CREATE or replace PROCEDURE Proc_Delete(v_sess_id varchar2) AS
cursor cur_dtl_ftr is SELECT a_number anum,b_number bnum
FROM tbl_detail_filter where sess_id=v_sess_id;
recno number := 0;
begin
for c1 in cur_dtl_ftr loop
delete from tbl_detail_filter where a_number=c1.anum
and b_number=c1.bnum;
recno := recno + 1; -- Commit after every X records
if recno > 10 then
commit;
recno := 0;
end if;
end loop;
commit;
end;

Could you plz..help us to solve the problem..
thanking you..
sincerely
sridhar