-
ORA-04031: unable to allocate 8388608 bytes of shared memory ("large
Dear Users,
When I run the query with contained nested tables , I am getting this error :
ORA-04031: unable to allocate 8388608 bytes of shared memory ("large
pool","unknown object","hash-join subh","QERHJ Bit vector")
I tried to increase the sort area ,large pool and so on ,but I am encountering the same prob..
I am pasting the .ora file content along with it , I am expectin a solution ...
##############################################################################
# Copyright (c) 1991, 2001 by Oracle Corporation
##############################################################################
###########################################
# Archive
#######################################
log_archive_dest_1='LOCATION=/home/oracle/OraHome1/oradata/ccms/archive'
log_archive_format=%t_%s.dbf
log_archive_start=true
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=67108864
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/home/oracle/OraHome1/admin/ccms/bdump
core_dump_dest=/home/oracle/OraHome1/admin/ccms/cdump
timed_statistics=TRUE
user_dump_dest=/home/oracle/OraHome1/admin/ccms/udump
###########################################
# Distributed, Replication and Snapshot
###########################################
db_domain=""
remote_login_passwordfile=EXCLUSIVE
###########################################
# File Configuration
###########################################
control_files=("/home/oracle/OraHome1/oradata/ccms/control01.ctl", "/home/oracle/OraHome1/oradata/ccms/control02.ctl", "/home/oracle/OraHome1/oradata/ccms/control03.ctl")
###########################################
# MTS
###########################################
dispatchers="(PROTOCOL=TCP)(SER=MODOSE)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)", "(protocol=TCP)"
###########################################
# Miscellaneous
###########################################
compatible=9.0.0
db_name=ccms
###########################################
# Network Registration
###########################################
instance_name=ccms
###########################################
# Pools
###########################################
java_pool_size=147440512
#large_pool_size=10485760
shared_pool_size=53470912
shared_pool_reserved_size=2000102
#Above parameter added on 04/03/04
#shared_pool_size = 200010212
log_buffer=163840
###########################################
# Processes and Sessions
###########################################
processes=150
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
###########################################
# Resource Manager
###########################################
resource_manager_plan=SYSTEM_PLAN
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
#sort_area_size=536870912
sort_area_size=5642880912
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS
event="04031 trace name context forever, level 10"
Thanks and Regards
Ramesh
-
Re: ORA-04031: unable to allocate 8388608 bytes of shared memory ("large
So do you have a large pool or not?
In your init.ora you have:
#large_pool_size=10485760
Run:
show parameter large
and
select * from v$version; and post here the output
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
the key is shared_pool_size and restart the DB once you change it unlesss you're using spfile.
-
Originally posted by zainalj
the key is shared_pool_size and restart the DB once you change it unlesss you're using spfile.
The error message points rather to the large pool than the shared pool.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
than, increase large_pool_size.. once changed check back either it take effect or not
SQL> show parameter large_pool_size
-
which is exactly what Julian said .....
-
Thank you julian and zainalj for your valuable feedback , I ran what all you said me to ,and I am pasting the output over there...
I uncommented the large_pool_size parameter .. I restarted the database , but still I am getting the error :
what your feedback soon....
SQL> show parameter large_pool_size
unknown SHOW option "parameter"
unknown SHOW option "large"
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
PL/SQL Release 9.0.1.0.0 - Production
CORE 9.0.1.0.0 Production
TNS for Linux: Version 9.0.1.0.0 - Production
NLSRTL Version 9.0.1.0.0 - Production
Regards
Ramesh
-
Re: ORA-04031: unable to allocate 8388608 bytes of shared memory ("large
Originally posted by rameshbabumande
###########################################
# MTS
###########################################
dispatchers="(PROTOCOL=TCP)(SER=MODOSE)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)", "(protocol=TCP)"
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
#sort_area_size=536870912
sort_area_size= 5642880912
Why are u killing ur SGA? ..
Make it dedicated type, u will not see the error ..
Note :
* Is sort area size of 5GB really necessary ?
* Is Shared Pool Reserved Space of 20M necessary ?
Abhay.
Last edited by abhaysk; 03-08-2004 at 05:57 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
I have only 1GB space , since
ORA-04031: unable to allocate 8388608 bytes of shared memory ("large
pool","unknown object","hash-join subh","QERHJ Bit vector")
is coming , I increased the sort area , how can I make it dedicated type . Can u give me the instruction in detail..so that I can change in my .ora file ...
Waiting for your help...
Ramesh
-
Code:
###########################################
# MTS
###########################################
# Comment below to go for dedicated server type.
#dispatchers="(PROTOCOL=TCP)(SER=MODOSE)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)", "(protocol=TCP)"
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
# 50M of sort area is enough i guess
sort_area_size=52428800
# Also is shared pool reserved required
shared_pool_reserved_size=2000102
Please see Slimdave's Signature..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|