Re: [External] : Re: Open cursor error

I don't think there's anything that would have recorded the information you need. You might get lucky and spot a pointer to the guilty SQL_ID in the active session history. If you know when the problem happened and query dba_hist_active_sess_history for the 30 minutes (say) before the ORA-01000, aggregating by session_id and sql_id, then drill down on the high-frequenty sql_ids (for each session in turn) to see if each occurence of the sql_id shows a difference sql_exec_id, you may be lucky and find a session which executes a specific statement a large number of times. That MIGHT be the session (and statement) that got the ORA-01000 by not closing the cursor after executing it.

The trouble with this approach is that the dba_hist samples are every 10 seconds, so you'd have to be a bit lucky for this approach to give you any solid evidence, but I can't think of any alternatives for searching the past.

Regards
Jonathan Lewis

On Thu, 24 Aug 2023 at 19:54, Pap <oracle.developer35_at_gmail.com> wrote:

> Thanks Jonathan.
>
> Yes definitely this application has issues with the code. At any point in
> time, I am seeing the majority of cursor_type as "OPEN". However I was
> trying to get hold of the exact top sesion and related sql_text which
> caused the failure(ora-01000) in the past with ~1000 open_cursors for that
> session. Is there any way to get that historical open cursor information
> from dba_hist/awr views?
>
> On Thu, Aug 24, 2023 at 2:10 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> The "guilty" cursor_type being "OPEN" is what I wanted to see - if it had
>> been one of the internal types then you might have have uncovered an Oracle
>> bug, but simply "OPEN" means it's almost certainly an application coding
>> error of the type that Andy described. (viz: exceptions being raised and
>> cursors not being closed in the handler - e.g. when others then null;
>> another is the server-side code explicitly opening a cursor and passing the
>> cursor handle to the client-side and the client forgetting to close.)
>>
>> Pick a couple of sessions with a large number of cursors of type OPEN,
>> and aggregate the SQL_ID - the results may be interesting, then check
>> username and sql_text. This may tell you almost everything you need to know
>> about where the error is coming from.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>

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