Re: Select statement hangs over DBlink (Intermittent)

On 9/27/05, Sami Seerangan <dba.orcl_at_gmail.com> wrote:

> QUERY
>
> c1 refcursor;
> c2 refcursor;
> BEGIN BIB_OTP_PKG.get_token_details('123678456',:c1,:c2); END;
>
> I am executing the stored procedure which includes select over DB Link.
> Most of the time it works (> 95%) and few times it hangs.
>
> I took 10053 event trace and it says wait event on 'SQL*Net message from
> dblink' ,'SQL*Net message to dblink' and 'SQL*Net break/reset to dblink'.
> Any idea what is going wrong here?

 EXEC
> #3:c=10000,e=509899355,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=4,tim=951620662363
> ERROR #3:err=2051 tim=97445955

  Well you are getting
 *ORA-02051 another session in same transaction failed *

*Cause:* A session at the same site with same global transaction ID failed.

*Action:* No action necessary; the transaction is automatically recovered. associated with cursor #3 which is your package. So you either don't have to do anything (which is what the error says) or I'd look into if there are either network issues, or the query is taking an inordinate amount of time for certain values of the binds which is causing a timeout somewhere.  It could be worse look at 2052..
*ORA-02052 remote transaction failure at string *

*Cause:* There was some error in a remote transaction at the named database link.

 --
> Niall Litchfield
> Oracle DBA
> http://www.niall.litchfield.dial.pipex.com

--
http://www.freelists.org/webpage/oracle-l