RE: ORA-01722 until shared pool is flushed

Thanks Dennis,

 

Several people have responded privately, including a few who experienced
similar problems but everyone either upgraded or retired the database.
One person had a resolution for the infrequent database link error that
we experience (setting an event on the 9i side) but since I haven't seen
that error in over 6 months I'm not going to bother implementing it.

 

I pinned the package two days ago and added it to our startup package
pinning routine so I'll see if that helps.  We don't usually get the
error more than once every week or two so it will be a while before I
can see if it helped and the database won't be restarted for another 6
weeks for it be pinned on startup (which I greatly prefer to pinning a
package after the database has been open for a long time).

 

What I find most interesting from the article you linked to is "It
generally happens in SQL only 
(during a query) not in plsql (plsql throws a different exception for
this 
error).  " since it's definitely happening from a pl/sql package.
 
 

 

 

Thanks,

Jay Miller

Sr. Oracle DBA

x68355

 

-----Original Message-----
From: Dennis Williams [mailto:oracledba.williams@gmail.com] 
Sent: Friday, March 31, 2006 11:33 AM
To: Miller, Jay
Cc: oracle-l@freelists.org
Subject: Re: ORA-01722 until shared pool is flushed

 

Jay,

 

I don't see where anyone responded to your posting, so I'll give less
than expert opinion.

So you're getting an "invalid number" error? Review this AskTom
discussion. Apparently this ORA-01772 can occur in some strange ways. 

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:45012

348053
Since you say that flushing the shared pool seems to alleviate the
problem for awhile, this problem might somehow be related to the shared
pool getting fragmented. You might consider pinning this stored
procedure in memory. 

 

Dennis Williams
 




-----------------------------------------
This message is confidential and sent by TD Waterhouse solely for
use by the intended recipient. If you are not the intended
recipient, you are hereby notified that any use, distribution or
copying of this communication is strictly prohibited. This should
not be deemed as an offer or solicitation, to buy or sell any
product. Any 3rd party information contained herein was prepared by
sources deemed reliable, but is not guaranteed. TD Waterhouse does
not accept electronic instructions that would require an original
signature. Information received by or sent from TD Waterhouse is
stored, subject to review, and may be produced to regulatory
authorities or others with a legal right to such.

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