Re: problem wit open cursors in 7.3.3.6.0 on HP 10.20

Hi Wim,

open_cursors is a per process limit. Every individual process can have 50 cursors open if open_cursors equals 50. A cursor will be setup for every individual sql statement. More cursors will be open when there are nested statements (example one loop fetching from a cursor, in the loop a second cursor is open to retrieve from a different table).
It looks like you had to shut because one of the parallel query slaves, a server side process was having problems.

Hth,

Sybrand Bakker, Oracle DBA

Wim Derweduwe wrote:

> Hallo,
>
> Yesterday we had a problem with our oracle DBMS
>
> All processes of oracle where still running. I cpould see them with ps but
> they did nothing. No one could connect to the database. Only with the
> servermanager you could connect tothe database. There you could see that
> there where 13 user on the database. The maximum cursors was on 50 while
> in v$open_cursor the there where 61 cursors used. One process used 45
> cursors.
>
> In the log we found the following lines:
> Errors in file /var/opt/oracle/rdbms/log/ora_18582.trc:
> ORA-12801: error signaled in parallel query server P015
> ORA-01652: unable to extend temp segment by 2560 in tablespace TEMP
> Mon Mar 8 14:55:56 1999
> Errors in file /var/opt/oracle/rdbms/log/ora_16800.trc:
> ORA-00600: internal error code, arguments: [1113], [], [], [], [], [], [],
> []
> ORA-01000: maximum open cursors exceeded
>
> Killing that user session didn't work. It freed the number of cursors till
> 35 but in total there where still 50 cursors open.
> We tried to kill all sessions but none would disappear. Finally we had to
> bring the database down.
>
> Any one known hows this could happen?
> And two other questions, why can there be 61 open cursors while the
> maximum is set to 50.
> And when does oracle give a process a cursor. And can a process get morre
> then one cursor. So when give oracle how many cursors to a user.
>
> Thanks
>
> Wim

Received on Tue Mar 09 1999 - 16:12:22 CST