You didn't specify exact Oracle version you are using (all 5 digits).
Besides, 7445 means a bug in Oracle software and you need to
open a TAR with Oracle Support if this bug is not fixed with current
patchset for your version and platform and your version is still
supported.
And I see several problems with your application as you described
it:
1) there is DDL running on presumably production system.
2) your application does not use bind variables by itself - time for
reengineering it, as cursor_sharing is not a silver bullet and can do
more harm than good (in your case, it is a bug, but impact of this
parameter is deeper than it seems - it may take optimal queries and
rewrite them so that their plans are awful since it is not always good
to bind everything).
You may want to set cursor_sharing at session level (through
AFTER LOGON database trigger or in application connect code) and
turn it off at instance level - this will minimize the impact of the
setting for other users/applications. But ultimately the only good
solution is to rewrite the application so it makes use of bind variables
by itself where appropriate and keep cursor_sharing at its default
setting of exact.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Syd H. Shah Khan" <habeeb_shahkhan_at_hotmail.com> wrote in message
news:ae0b2c43.0206170003.d957c11_at_posting.google.com...
> I think I have narrowed the problem down to a certain extent. When
> the SQL in the library cache is invalidated, then when the same piece of code
> is rerun, it hits an ORA-7445 error.
>
> As long as the SQL is not invalidated I can run the code several times. But
> once it is invalidated (due to DDL on dependent object), Oracle cannot reprase
> the same statement and hits and ORA-7445 error.
>
> The parameter cursor_sharing is set for force. And the server is running MTS.
> The only workaround to the problem is to set cursor_sharing = exact. I want to
> make use of cursor_sharing = force since the application does not make use of
> bind variables and therefore cursors are not being reused/shared.
> Thank you in advance.
Received on Mon Jun 17 2002 - 07:57:16 CDT
