Home
All Oracle Error Codes
Oracle DBA Forum

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

JayMiller

2006-03-29

Replies:
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.

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