| Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown object [message #64767] |
Fri, 23 January 2004 20:19  |
Arvind Bhandari
Messages: 50
Registered: May 2003
|
Member |
|
|
Hello, we are using Oracle 9i Database and Report 3.0 but there is some problem related with Shared memory after running SQL. When we run following SQL in SQL and Report 3.0 then it gives following Error
ERROR at line 2:
ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown object","hash-join subh","kllcqas:kllsltba")
SELECT NVL(SUM(A.Loan_Amt),0)
FROM BMHLACC A,BTDLLED B,BMDPRIGRP C
WHERE A.Branch_Cd = '03'
AND A.Acc_Type = B.Acc_Type
AND A.Acc_No = B.Acc_No
AND A.Branch_Cd = C.Branch_Cd
AND A.Pri_Grp_Cd = C.Pri_Grp_Cd
AND A.Purpose_Cd = C.Pur_Cd
AND TRUNC(C.Eff_Fr) = (SELECT TRUNC(MAX(Eff_Fr))
FROM BMDPRIGRP
WHERE Branch_Cd = A.Branch_Cd
AND Pri_Grp_Cd = A.Pri_Grp_Cd
AND Pur_Cd = A.Purpose_Cd
AND TRUNC(Eff_Fr) <= To_Date('31-Dec-2003'))
AND A.Loan_Amt <= C.Pri_Limit
AND ((A.status = 'O') OR
( (A.Acc_Type,A.Acc_No) in (select acc_type,acc_no
from bmcllacc
where trunc(Trans_DT) > To_Date('31-Dec-2003') AND Status = 'A'))
)
AND B.Bal_Amt >= 0
-- AND A.Pri_Grp_Cd = :Pri_Grp_Cd
AND TRUNC(A.Open_Dt) <= To_Date('31-Dec-2003')
AND A.Catg_Cd IN('GN','WM','SC')
AND Trunc(b.trans_DT) = (select trunc(max(trans_DT))
from btdlled
where acc_type = a.acc_type
and acc_no = a.acc_no
and trunc(trans_DT) <= To_Date('31-Dec-2003'))
and b.sno = (select max(sno)
from btdlled
where acc_type = a.acc_type
and acc_no = a.acc_no
and trunc(trans_DT) = trunc(b.trans_DT))
In init.ora the parameter for share_pool_size has set to
shared_pool_size=33554432
open_cursors=300
When we tried to change open cursor parameter then it's not change. When we see on Enterprise Manager console, the Configration parameter shown dynamicaly for both the parameter.
can we change both paramter or it automatically change dynamically in oracle 9i ?
Thanks
Arvind
|
|
|
|
| Re: Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown ob [message #64768 is a reply to message #64767] |
Sat, 24 January 2004 04:10  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi,
you may get this error when
i) shared_pool_size is 'really' insufficient for the workload
ii) shared_pool, gets fragmented for some reasons(tons of unique sql becos of lack of bind variables,huge anonymous pl/sql, UGA of Shared servers being allocated in shared pool instead of large pool etc)
In your case,it may be that you have insufficient shared_pool_size and hence you can increase it .
thiru@9.2.0:SQL>show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 33554432
thiru@9.2.0:SQL>alter system set shared_pool_size=35m;
System altered.
thiru@9.2.0:SQL>show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 37748736
thiru@9.2.0:SQL>
You dont have to increase open_cursors in this case.
-Thiru
|
|
|
|
| Re: Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown ob [message #64772 is a reply to message #64768] |
Tue, 27 January 2004 02:19 |
Arvind Bhandari
Messages: 50
Registered: May 2003
|
Member |
|
|
Thanks Thiru, actually shared_pool_size was not increased through alter system in SQL session but in Oracle enterprise manager console i have increase the MAX SGA Size and increase shared_pool_size and large_pool_size. after that query runs. but one doubt again that when we increased the MAX_SGA_SIZE parameter it automatic increase the value of shared_pool_size. after that we have substact 10 MB from shared_pool_size and added to large_pool_size.
once again thanks. if server manager is not available in oracle 9i then how can we shutdown and startup database as in oracle 8i we can do by running svrmgrl
Arvind
Arvind
|
|
|
|
| Re: Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown ob [message #64775 is a reply to message #64772] |
Tue, 27 January 2004 07:20 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Arvind,
you use sqlplus to startup/shutdown database
dbatest@samgdeab06:/ora1/oracle/app/oracle/admin/dbatest/udump>sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Jan 27 12:23:27 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 336679728 bytes
Fixed Size 455472 bytes
Variable Size 117440512 bytes
Database Buffers 218103808 bytes
Redo Buffers 679936 bytes
Database mounted.
Database opened.
-Thiru
|
|
|
|
| Re: Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown ob [message #64777 is a reply to message #64775] |
Wed, 28 January 2004 02:50 |
Arvind Bhandari
Messages: 50
Registered: May 2003
|
Member |
|
|
thanks again thiru. i have tried to shutdown through SQLp plus but my machine hangs at that time. anyway i shall try again. if we shutdown the database through SQL plus and exits from the session then how can we again connect SQL plus session. as in ORACLE 8i SERVER MANAGER runs even database shut down. but in ORACLE 9i SQL session will not work if database down
yesterday we have changed that open_cursors and Shared_pool_size but today again set to as it was in previous. when we see in init.ora then it shows previous entry. I think yesterday changed specific for that session only and it stores in session's initial parameter file. so how can we change parameters direcly in init.ora. as in oracle 8i we first shutdown the database through server manager and change mannualy in initorcl.ora and startup the database.
how can we increase the SGA and shared_pool_size permanently in init.ora file
Thanks again thiru
Arvind
|
|
|
|
| Re: Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown ob [message #64778 is a reply to message #64777] |
Wed, 28 January 2004 04:58 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Arvind,
to establish a sqlplus connection , without actually establishing a session to the database ,
DEV08@samgdeab06:/ora1/oracle/app/oracle/admin/dbatest/bdump>sqlplus /nolog
SQL*Plus: Release 9.0.1.4.0 - Production on Wed Jan 28 09:44:22 2004
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL>
that will work even when the database is down.
There is no separate session parameter file . There is only one parameter file ( init.ora or spfile.ora) where the instnace configuration parameters are configured.
Some parameters are session modifiable and some are only system modifiable(ie requires an instance bounce to take effect).
If you are using init.ora and you want to make the changes permanent, you will need to edit the init.ora manually and bounce the instance.
If you are using spfile, then the following rules apply :
SCOPE Clause Description
SCOPE = SPFILE
The change is applied in the server parameter file only. The effect is as follows:
For dynamic parameters, the change is effective at the next startup and is persistent.
For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.
SCOPE = MEMORY
The change is applied in memory only. The effect is as follows:
For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
For static parameters, this specification is not allowed.
SCOPE = BOTH
The change is applied in both the server parameter file and memory. The effect is as follows:
For dynamic parameters, the effect is immediate and persistent.
For static parameters, this specification is not allowed.
It is an error to specify SCOPE=SPFILE or SCOPE=BOTH if the server is not using a server parameter file. The default is SCOPE=BOTH if a server parameter file was used to start up the instance, and MEMORY if a traditional initialization parameter file was used to start up the instance.
Hope this clarifies..
Thiru
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
