ORA-01722 until shared pool is flushed
- From: <JayMiller@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Mar 2006 18:36:44 -0500
We have an odd issue that has gotten worse as of yesterday. We have an
8.1.7.2 database that is very important but cannot be upgraded or
patched (it's being retired in about a year and would require about 6
months of testing for all the applications that rely on it).
At irregular intervals (2-5x/month) one of the stored procedures will
start returning an ORA-01722. If the shared pool is flushed the problem
goes away.
Since we had a quick workaround of flushing the shared pool and the
database is being retired anyway I wasn't too worried but as of
yesterday flushing the shared pool no longer worked and I also had to
kill the sessions connecting as that user (we have a middle tier with
connection pooling using websphere).
The error occurs at random times of day (always during business hours),
but became especially prevalent during a period when we accidently had
statistics on some data dictionary objects. This obviously caused
general performance problems but also the 01722 error started happening
daily instead of every few weeks.
No trace files or errors in the alert log. A level 12 trace while the
error was occurring turned up the following:
PARSING IN CURSOR #265 len=218 dep=1 uid=8 oct=3 lid=8 tim=2689309642
hv=1964772
655 ad='d9ff5f14'
SELECT CAR.CUSTOMER_ID,
CAR.ACCOUNT_ID,
CAR.CUST_ROLE
FROM CUSTOMER_ROLE CR,CUST_ALT_ROLE CAR
WHERE CR.ACCOUNT_ID = :b1
AND CR.CUST_ROLE = :b2
AND CAR.ACCOUNT_ID = CR.ACCOUNT_ID
AND CAR.CUSTOMER_ID = CR.CUSTOMER_ID
END OF STMT
EXEC #265:c=5,e=4,p=0,cr=51,cu=0,mis=1,r=0,dep=1,og=4,tim=2689309642
ERROR #265:err=1722 tim=2689309642
EXEC #34:c=45,e=46,p=0,cr=500,cu=71,mis=0,r=0,dep=0,og=4,tim=2689309642
ERROR #34:err=20020 tim=2689309642
WAIT #255: nam='latch free' ela= 0 p1=-453714724 p2=106 p3=0
WAIT #255: nam='latch free' ela= 0 p1=-453714724 p2=106 p3=1
The code associated with this is as follows (part of a much larger
package):
BEGIN
SELECT car.customer_id, car.account_id, car.cust_role
INTO lv_cr_customer_id, lv_cr_account_id, lv_cr_cust_role
FROM customer_role cr, cust_alt_role car
WHERE cr.account_id = lv_account_id
AND cr.cust_role = i_cust_role
AND car.account_id = cr.account_id
AND car.customer_id = cr.customer_id;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
The variables have the datatypes assigned correctly and anyway it starts
working after the shared pool is flushed so it doesn't seem like a
programming bug.
The only thing that seems related is that we occasionally (but much more
rarely) get an ORA-01722 when we access a 9.2.0.4 database through a
database link. At that point I was able to duplicate the error by
running an anonymous pl/sql block but the same SQL run from the SQL*Plus
prompt ran correctly without errors. Unfortunately I didn't save that
SQL and don't recall it offhand (we haven't had the error in over 6
months). Those errors only started after the remote database was
upgraded to 9i (we had a similar problem from an 8.1.6 database that was
connecting to the 9.2.0.4 database until it was upgraded to 9i).
System details:
Database with error:
Solaris 2.8
Oracle 8.1.7.2
Remote database that caused similar error (not called from current error
but maybe it's significant?):
Solaris 2.9
Oracle 9.2.0.4
Ideas?
Thanks,
Jay Miller
-----------------------------------------
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
