Re: how to trace OCIStmtExecute failed ORA-00028: your session has been killed

Hi Krish/Jared,

As you suggested we did a extensive search on all the codes that runs on the db and we found out that there was a program unit which was dynamically creating alter system from inside a cron job and killing all the sessions which appears during the time it runs in v$lock . after commenting out this piece of code we no more getting ORA-28 . Thanks a lot..

Wish you all merry christmas.

thanks
Prasad

On Dec 17, 2007 5:06 PM, Prasad <p4cldba_at_gmail.com> wrote:

> sorry forgot to mention that there are alter system kill session sql
> during that time when the client process dies .
>
> SQL> select sql_text from v$sqlarea where sql_text like 'alter system%' ;
>
> ALTER SYSTEM kill SESSION '627,188'
>
> ALTER SYSTEM kill SESSION '620,1950'
>
>
> On Dec 17, 2007 4:59 PM, Prasad <p4cldba_at_gmail.com> wrote:
>
> > Hi Krish,
> >
> > Oracle support took a while to get back to me on this . and there
> > research is that the current 9.2 client is affected by the below bugs .
> >
> > Bug.4952119 (90) SQLNET.OUTBOUND_CONNECT_TIMEOUT NOT CAN CELLED AFTER
> > CONNECTION ESTABLISHMENT:
> > Bug 4933023. Bequeath connections fail if
> > SQLNET.OUTBOUND_CONNECT_TIMEOUT is set
> > and these bugs are Fixd in 10.2.0.3.
> >
> > and they want me to upgrade it to 10.2.0.3 . I will update my findings
> > .
> >
> > thanks
> > -Prasad
> >
> >
> >
> > On Dec 13, 2007 11:11 PM, < krish.hariharan_at_quasardb.com > wrote:
> >
> > > Didn't solve the mystery but eliminated a couple of paths since I was
> > > curious about the error. If you have resource limit set and profile with
> > > idle time, or your server process was killed, you get a different error than
> > > the ORA-28. It then appears that only a kill session tends to yield this
> > > error.
> > >
> > >
> > >
> > > I am curious, however, about the timing of the message and the error
> > > stack. These errors are not reported until the client tries to act again.
> > > Would that still maintain information about the circumstances that caused
> > > the disconnect?
> > >
> > >
> > >
> > > -Krish
> > >
> > >
> > >
> > > Not sure if you had already checked the sql area and su log
> > >
> > >
> > >
> > > SQL> select sql_text from v$sqlarea where sql_text like 'alter
> > > system%' ;
> > >
> > >
> > >
> > > SQL_TEXT
> > >
> > >
> > > --------------------------------------------------------------------------------
> > >
> > > alter system kill session '28,29909'
> > >
> > > alter system set resource_limit = true
> > >
> > >
> > >
> > >
> > >
> > > IDLE Time
> > >
> > > ---------------
> > >
> > > SQL> select to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS') cdate from dual
> > > ;
> > >
> > > select to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS') cdate from dual
> > >
> > > *
> > >
> > > ERROR at line 1:
> > >
> > > ORA-02396: exceeded maximum idle time, please connect again
> > >
> > >
> > >
> > > Kill Session
> > >
> > > ------------------
> > >
> > > SQL> select to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS') cdate from dual
> > > ;
> > >
> > > select to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS') cdate from dual
> > >
> > > *
> > >
> > > ERROR at line 1:
> > >
> > > ORA-00028: your session has been killed
> > >
> > >
> > >
> > > Server process killed
> > >
> > > --------------------------------
> > >
> > > SQL> select sysdate from dual ;
> > >
> > > select sysdate from dual
> > >
> > > *
> > >
> > > ERROR at line 1:
> > >
> > > ORA-03113: end-of-file on communication channel
> > >
> >
> >
>

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