Recently, I faced off with this wait eevent. I read about in Tanel Poder’s blog to clarify what this event means.
Unfortunately, the reason of this event I didn’t find. In documentation:
SQL*Net break/reset to client
The server is sending a break or reset message to the client. The session running on the server is waiting for a reply from the client.These waits are caused by an application attempting to:
Select from a closed cursor
Select on a cursor after the last row has already been fetched and no data has been returned
Select on a non-existent table
Insert a duplicate row into a uniquely indexed table
Issuing a query with invalid syntax
If the value, v$session_wait.p2, for this parameter equals 0, it means a reset was sent to the client. A non-zero value means that the break was sent to the client.
Ok, I want to find SPID process on the server for these waintaing sessions, but no luck! In v$session, p2 for these sessions are 0. Select statement:
select p.spid from v$process p, v$session s where p.addr=s.paddr and s.sid in (163,245);
returns no rows.
Next I tried this:
select spid,program,username from v$process p where not exists (select * from v$session s where p.addr=s.paddr);
SPID PROGRAM USERNAME
—– —————————————- ———-
30845 oracle@com (D000) oracle
30849 oracle@com (S000) oracle
20477 oracle@com (PZ97) oracle
1812 oracle@com (PZ99) oracle
917 oracle@com (PZ98) oracle
24594 oracle@com (S003) oracle
4975 oracle@com (S002) oracle
27199 oracle@com oracle
27961 oracle@com oracle
Aha, may be 27199 and 27961 are these one? Yes, they are! Kill -9 help me to resolve this issue.