Re: [External] : Re: Open cursor error

To be honest I did not follow this thread completely and so I might have missed something but why not just setting up a trap (e.g. something like this "SQL> alter system set events '1000 trace name cursordump level 1, lifetime 1';") and wait for next occurrence?

Thanks.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: www.soocs.de
Twitter: _at_OracleSK

> Jonathan Lewis <jlewisoracle_at_gmail.com> hat am 29.08.2023 19:28 CEST geschrieben:
>
>
> 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

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