ORA-01722 until shared pool is flushed

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

ORA-01722 until shared pool is flushed

ORA-01722 until shared pool is flushed

2006-04-05 - By JayMiller@(protected)

Reply: 1 2 3 4 5 6

Okay, the problem recurred today and I was surprised to see that
flushing the shared pool resolved the problem again. Since the package
which is generating the error is now pinned it would not be cleaned out
by the shared pool flush. I'm stumped.

-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]
Sent: Friday, March 31, 2006 1:51 PM
To: oracle-l@(protected)
Subject: RE: ORA-01722 (See ORA-01722.ora-code.com) 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@(protected)]
Sent: Friday, March 31, 2006 11:33 AM
To: Miller, Jay
Cc: oracle-l@(protected)
Subject: Re: ORA-01722 (See ORA-01722.ora-code.com) 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 (See ORA-01772.ora-code.com) 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.

<html>

<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">

<meta name=Generator content="Microsoft Word 10 (filtered)">

<style>

</style>

</head>

<body lang=EN-US link=blue vlink=blue>

<div class=Section1>

Okay, the problem recurred today and I was
surprised to see that flushing the shared pool resolved the problem again. 
Since the package which is generating the error is now pinned it would not be
cleaned out by the shared pool flush.    I’m stumped.

 

 

 

 

 

 

-- --Original Message-- -- 
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] 
Sent: Friday, March 31, 2006 1:51
PM 
To: oracle-l@(protected) 
Subject: RE: ORA-01722 (See ORA-01722.ora-code.com) 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).


 

<pre style='margin-left:.5in'>What I find most
interesting from the article you linked to is “It generally
happens in SQL only </pre><pre
style='margin-left:.5in'>(during a query) not in plsql (plsql throws a
different exception for this </pre><pre
style='margin-left:.5in'>error).  ” since it’s
definitely happening from a pl/sql package.</pre><pre
style='margin-left:.5in'> </pre
><pre
style='margin-left:.5in'> </pre>


 


 

<div>

Thanks,

Jay
Miller

Sr.
Oracle DBA

x68355

</div>


 

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

 

<div>

Jay,

</div>

<div>

 

</div>

<div>

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

</div>

<div>

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

</div>

<div>

<a
href="http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID
:45012348053">http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID
:45012348053</a> 
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. 


</div>

<div>

 

</div>

<div>

Dennis Williams 
 

</div>

</div>

</body>

</html>
<HTML><BODY><hr size=1> 
 
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. 
</BODY>