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: Shared pool from 8i to 9i

mkline1

2005-01-24

Replies:
And where is the "alter shared_pool coalesce;" ????

:-)

Michael Kline
Principal Consultant
Business to Business Solutions
13308 Thornridge Ct
Midlothian, VA 23112
O: 804.744.1545
Fax: 804.763.0114

-----Original Message-----
From: oracle-l-bounce@(protected)
Behalf Of Subbiah, Nagarajan
Sent: Monday, January 24, 2005 10:56 PM
To: 'Alexander Gorbachev'; Subbiah, Nagarajan
Cc: Wolfgang Breitling; Paula_Stankus@(protected);
BSpears@(protected)
Subject: RE: Shared pool from 8i to 9i

This could be the reason running the query in every 15 minutes to monitor
the shared_pool fragmentation, gives the ORA-4030 error. I think as the
library cache latch contention causes the performance issue and the number
or sessions are increased and then ORA-4030 appears.

I am interested to know how to avoid the ORA-4031 error. Even if this query
gives the shared_pool fragmentation chunks, Do we have any control over the
de-fragmentation? By default LRU algorithm is being used to find the
contiguous required space for the SQL queries.

Can we have different sub pools of shared pool and specify the range for the
SQL size to go to the particular sub pool? I know that the
shared_pool_reserved_min_alloc parameter helps to certain extent. Is there
any view to find out what is the usage of the reserved size of the
shared_pool and how to find out the optimal value of the
shared_pool_reserved_size?

We are on HP-UX 11.11 and Oracle 9.2.0.5

Raja

> -----Original Message-----
> From:  Alexander Gorbachev [SMTP:gorbyx@(protected)]
> Sent:  Monday, January 24, 2005 7:06 PM
> To:  Nagarajan.Subbiah@(protected)
> Cc:  Wolfgang Breitling; Paula_Stankus@(protected);
> BSpears@(protected);
> oracle-l@(protected)
> Subject:  Re: Shared pool from 8i to 9i
>
> Hi,
> I suggest to be very careful about using this query. We have run into
> a nasty bug queryng X$KSMSP view in our monitoring job that determined
> shared_pool fragmentation. The problem was that as usage of
> shared_pool was growing most of our sessions were "hanging" for a
> while on latch free on shared_pool_latch and library_cache_latch. It
> turned out that the statement on this view was the holder of the
> latch. Ref bug 3938739.
> Our platform is HP-UX and we are on 9.2.0.5.
>
> Cheers,
> Alex
>
> > select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
> "From",
> > count(*) "Count" , max(KSMCHSIZ) "Biggest",
> > trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
> > from x$ksmsp
> > where KSMCHSIZ<140
> > and KSMCHCLS='free'
> > group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
> > UNION ALL
> > select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
> > count(*) , max(KSMCHSIZ) ,
> > trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
> > from x$ksmsp
> > where KSMCHSIZ between 140 and 267
> > and KSMCHCLS='free'
> > group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
> > UNION ALL
> > select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
> > count(*) , max(KSMCHSIZ) ,
> > trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
> > from x$ksmsp
> > where KSMCHSIZ between 268 and 523
> > and KSMCHCLS='free'
> > group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
> > UNION ALL
> > select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX,
> 500*trunc(KSMCHSIZ/500)
> > ,
> > count(*) , max(KSMCHSIZ) ,
> > trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
> > from x$ksmsp
> > where KSMCHSIZ between 524 and 4107
> > and KSMCHCLS='free'
> > group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
> > UNION ALL
> > select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX,
> 1000*trunc(KSMCHSIZ/1000) ,
> > count(*) , max(KSMCHSIZ) ,
> > trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
> > from x$ksmsp
> > where KSMCHSIZ >= 4108
> > and KSMCHCLS='free'
> > group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);
--
http://www.freelists.org/webpage/oracle-l

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