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

Re: Strange 4031 issue.

Jonathan Lewis

2006-11-02

Replies:

The amount of memory free is nearly irrelevant if you want
to allocate a single large chunk. It is common for the heap
to end up as a very large collection of very small pieces,
and if a lot of those pieces are pinned in some way, it
may not be possible to free enough adjacent pieces to
produce a single chunk large enough.

27K is about the size of the 'session parameters' on some
platforms, so if you keep seeing the same size chunk
causing a failure then perhaps that's what it is. You could
try increasing your reserved_shared_pool so that it is
large enough to hold sessions * 27KB plus a few dozen
MB.

Cross-check you timestamps on the traces with the
large amounts of free memory. A hunt for free memory
can clear a lot of stuff from the shared pool, and it
can take some time to reload. Do you any of the
reports of large percentages of free memory show up
a few minutes after a trace file is dumped ?


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


----- Original Message -----
From: "Wolfson Larry - lwolfs" <lawrence.wolfson@(protected)>
To: <oracle-l@(protected)>
Sent: Thursday, November 02, 2006 9:36 PM
Subject: Strange 4031 issue.


9.2.0.6

  Getting multiple 4031s on SQLLDR processes.
Shows up as
-rw-r--r--  1 dba       14755 Oct 31 14:08 p_j013_834.trc
-rw-r--r--  1 dba       14880 Oct 31 14:06 p_j004_29359.trc
-rw-r--r--  1 dba       14887 Oct 31 13:58 p_j008_23565.trc
-rw-r--r--  1 dba       14763 Oct 31 13:56 p_j000_22346.trc
-rw-r--r--  1 dba       14764 Oct 31 13:53 p_j011_19931.trc
-rw-r--r--  1 dba       14766 Oct 31 13:51 p_j005_18595.trc

  like it came from dba_jobs.
We've been monitoring SP every 15 minutes and it looks like there's
generally lots of memory available.

DB  TIME           Shared Pool Size     Free Bytes Percent Free
---- -------------------- ---------------- ---------------- ------------
XXXX 25-OCT-2006 07:15:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 07:30:00    167,772,160     8,299,488   4.9468803
XXXX 25-OCT-2006 07:30:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 07:45:00    167,772,160     14,386,336   8.5749244
XXXX 25-OCT-2006 07:45:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 08:00:00    167,772,160     12,982,504   7.7381753
XXXX 25-OCT-2006 08:00:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 08:15:00    167,772,160     25,204,720  15.0231838
XXXX 25-OCT-2006 08:15:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 08:30:00    167,772,160     8,200,736   4.8880195
XXXX 25-OCT-2006 08:30:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 08:45:00    167,772,160     13,062,288   7.7857303
XXXX 25-OCT-2006 08:45:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 09:00:00    167,772,160     10,246,144   6.1071777
XXXX 25-OCT-2006 09:00:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 09:15:00    167,772,160     9,018,072   5.3751897
XXXX 25-OCT-2006 09:15:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 09:30:00    167,772,160     28,939,400  17.2492266
XXXX 25-OCT-2006 09:30:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 09:45:00    167,772,160     21,258,496  12.671051
XXXX 25-OCT-2006 09:45:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 10:00:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 10:00:00    167,772,160     52,412,056  31.2400198
XXXX 25-OCT-2006 10:15:00    167,772,160     28,882,048  17.2150421
XXXX 25-OCT-2006 10:15:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 10:30:00    167,772,160     15,986,504   9.5286989
XXXX 25-OCT-2006 10:30:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 10:45:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 10:45:00    167,772,160     51,772,144  30.8586025
XXXX 25-OCT-2006 11:00:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 11:00:00    167,772,160     34,621,608  20.6360865
XXXX 25-OCT-2006 11:15:00    167,772,160     8,650,376   5.1560258
??
XXXX 25-OCT-2006 11:15:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 11:30:00    167,772,160     9,714,240   5.7901382
??
XXXX 25-OCT-2006 11:30:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 11:45:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 11:45:00    167,772,160     57,708,392  34.3968821
XXXX 25-OCT-2006 12:00:00    167,772,160     28,971,008  17.2680664
XXXX 25-OCT-2006 12:00:00    167,772,160     37,005,832  22.0571947

Vendor keeps saying nothing special happens at times we experience
4031s or dips in available memory

I summarize what's in SP and I don't see anything really big

TYPE                 NAME
MEMORY KEPT
---------------------------- ----------------------------------------
------------ ---
JAVA CLASS             sun/io/CharacterEncoding
109,204 YES
PACKAGE BODY           S2P_INV_CTL
109,978 NO
PACKAGE               S2P_C
116,318 NO
PACKAGE BODY           S2P_ORDER_CTL
116,835 NO
PACKAGE               S2P_RF_UTIL
117,385 NO
JAVA CLASS             java/util/TimeZoneData
121,338 YES
PACKAGE BODY           S2P_SKUXLOC
122,842 NO
PACKAGE BODY           S2P_TASKHDR
123,554 NO
PACKAGE BODY           S2P_RF_PICK
126,569 NO
CURSOR               SELECT *  FROM ORDERS WHERE ORDERKEY =
127,326 NO
                   :b1
PACKAGE BODY           S2P_RF_UTIL
127,705 NO
PACKAGE BODY           S2P_TASKDTL
128,138 NO
JAVA CLASS             /1adcad9a_OracleStatement
132,077 YES
PACKAGE BODY           S2P_LOC
137,626 NO
PACKAGE BODY           S2P_ORDERS
150,408 NO
PACKAGE BODY           S2P_TASK
157,804 NO
CURSOR               SELECT *  FROM TASKHDR WHERE TASKHDR.D
158,892 NO
                  EVICE = :b1 AND TASKHDR.STATE = :b2 AN
                  D TASKHDR.STS = :b3 ORDER BY TASKHDR.PIC
                  KEDUPSEQUENCE DESC
PACKAGE BODY           S2P_STORAGE
161,386 NO
PACKAGE               S2P_RF_TASK
163,922 NO
PACKAGE BODY           S2P_RF_AUTO
165,138 NO
PACKAGE BODY           S2P_TASKTRN
166,442 NO
PACKAGE BODY           S2P_CONT_INV_CTL
184,500 NO
PACKAGE BODY           S2P_INVENTORY
188,598 NO
PACKAGE BODY           S2P_RF_TASK
251,338 NO
CURSOR               table_1_0_141_0_0_0
353,117 NO
PACKAGE BODY           S2P_TASKTRN_CTL
359,722 NO
PACKAGE               STANDARD
438,556 NO

Then we get 4031s on sqlplus jobs, but connected users don't see this.
OOPS! Actually we did see ONE 4031 from connected user (out of 330) at
this time.

And same size every time:
ORA-04031: unable to allocate 27512 bytes of shared memory

We're tracking all errors:
create or replace trigger log_errors_trig
after servererror on database
declare
var_user   varchar2(30);
var_osuser  varchar2(30);
var_machine varchar2(64);
var_process varchar2(09);
var_program varchar2(48);
var_sqltext varchar2(4000);
sql_text   ora_name_list_t;
n        number;
begin

Saw Karen Morton suggested log_buffer might be too small. It's 512K
here, but haven't increased it.

Did see someone complain about SP stress when running sqlldr in 8.1.5
Is there a parm that can cause problem?

Saw someting about perfstat reporting miscellanrous growth over time,
but don't have perfstat installed here (yet).

I'll see if I can get script from vendor.

Any ideas?


  Thanks
  Larry



***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
****************************************************************************
--
http://www.freelists.org/webpage/oracle-l




--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.13.22/512 - Release Date: 01/11/2006


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