First of all, I'd like to thank you all who contributed with this thread. After some tests, last Monday I found the problem: there was a bug in the stored procedure that was leading to a endless loop. The most important things I have learned: - the difference between -2, 0 e >0 in v$session_wait.wait_time for online monitoring; - probably you are not hitting a bug; - don't trust developers when they say they have debugged the code and it's still freezing without explanation. Debug it yourself. Best regards, Luis > -----Original Message----- > From: Luis Fernando Cerri > Sent: quinta-feira, 19 de janeiro de 2006 14:46 > To: oracle-l@xxxxxxxxxxxxx > Subject: File open wait > > Hello, list! > > OS: HPUX 11.11 > Oracle 8.1.7.4 EE > > > > I'm facing right now a "file open" wait for a specific session as follows: > > This select is returning the same result for like one hour: > > select substr(event,1,35) event,TOTAL_WAITS > from v$session_event > where sid= 40; > > EVENT TOTAL_WAITS > ----------------------------------- ----------- > log file sync 1 > db file sequential read 12 > file open 2 > SQL*Net message to client 38 > SQL*Net message from client 38 > > > > Current active wait event: > > SQL> select seq#,event,wait_time,seconds_in_wait,state > 2 from v$session_wait > 3 where sid=40 > 4 / > > SEQ# EVENT WAIT_TIME SECONDS_IN_WAIT STATE > ----- ------------ ---------- --------------- ------------------- > 91 file open -2 4751 WAITED UNKNOWN TIME > > > > > No join information between v$session and dba_data_files: > > SQL> select s.ROW_WAIT_FILE#,f.file_name > 2 from v$session s, dba_data_files f > 3 where s.ROW_WAIT_FILE# = f.file_id > 4 and s.sid=40 > 5 / > > no rows selected > > SQL> select s.ROW_WAIT_OBJ#,s.ROW_WAIT_FILE# > ,s.ROW_WAIT_BLOCK#,s.ROW_WAIT_ROW# > 2 from v$session s > 3 where s.sid=40; > > ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# > ------------- -------------- --------------- ------------- > -1 0 0 0 > > > > Enabling 10046 trace through dbms_support didn't generated a trace file on > UDUMP destination. The ORADEBUG DUMP PROCESSSTATE didn't return usable > information (or I couldn't find it). > > > Any guesses on determining which file is the session waiting for? Do P1, > P2 and/or P3 mean anything usable? Oracle states that this information can > be used only by development team. > > Thanks in advance for your collaboration. > > Best regards, > Luis >