-
ORA-04031: unable to allocate 4032 bytes of shared memory
hi,
I tried to export my instance oracle:
EXP SYSTEM/MANAGER@MYDB FULL=Y FILE=C:\MYDB\EXP.DMP LOG=C:\MYDB\ERROR_EXP.LOG
but I get this error:
xporting table WYTYPE 0 rows exported
. exporting referential integrity constraints
. exporting synonyms
EXP-00008: ORACLE error 4031 encountered
ORA-04031: unable to allocate 4032 bytes of shared memory ("large pool","unknown object","joxu heap init","ioc_allocate_pal")
EXP-00000: Export terminated unsuccessfully
How can I resolve this problem??
Thanks in advance!
Raf
-
I remember when this place was cool.
-
I view metalink site, but what id number has my specific problem??
-
Your SGA has become fragmented ...
Your will have to restart the database to clear up the fragmentation.
Your should attempt to pin some of the objects in memory that
are getting reloaded multiple times... See script below to view
the reloads and sizes of objects...
You also may need to look at resizing your SGA
Another aid is the following undocumented init parameter in 8.1.7
#### Work around for BAMIMA Buffer (4031) errors ####
_db_handles_cached = 0
To determine what large PL/SQL objects are currently loaded in the shared pool
and are not marked 'kept' and therefore may cause a problem, execute the following:
select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
from v$db_object_cache
where sharable_mem > 100
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
or type = 'PROCEDURE')
and kept = 'NO'
order by owner,loads desc;
--- Objects that have been reloaded ---
select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
from v$db_object_cache
where loads > 1
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
or type = 'PROCEDURE')
and kept = 'NO'
order by owner,loads desc;
Hope this helps
Gregg
-
Hi,
In the metalink, you will find a lot of such thread. I picked up from there..
Solution:
Well, still, shared pool is not big enough.
ORA-04031 unable to allocate string bytes of shared memory ("string","string","string","string")
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.
There is always a better way to do the things.
-
I remember when this place was cool.
-
If it seems unlikely that your SGA is undersized, you might be experiencing a bug from the early releases of 8.1.7
Patching to 8.1.7.4 might be a solution.
David Knight
OCP DBA 8i, 9i, 10g
-
I've this hardware and software:
Pentium 4 1,80 Ghz
521 Mb RAM
O.S. Windows XP
Oracle version: 9.0.1.0.1
Actually the parameters are:
resource_limit................ FALSE
license_max_sessions............. 0
license_sessions_warning.............. 0
cpu_count....................... 1
shared_pool_size................ 58720256
sga_max_size.................... 126644216
shared_pool_reserved_size....... 2306867
large_pool_size................. 0
java_pool_size.................. 33554432
java_soft_sessionspace_limit..... 0
java_max_sessionspace_size...... 0
lock_sga........................ FALSE
db_cache_size................... 33554432
log_buffer...................... 524288
transactions.................... 187
undo_retention.................. 10800
create_bitmap_area_size......... 8388608
bitmap_merge_area_size.......... 1048576
parallel_execution_message_size.. 2148
hash_join_enabled............... TRUE
hash_area_size................... 1048576
max_dump_file_size.............. UNLIMITED
oracle_trace_collection_size........ 5242880
object_cache_optimal_size.......... 102400
object_cache_max_size_percent....... 10
sort_area_size.................. 1048576
sort_area_retained_size................ 0
optimizer_max_permutations.......... 2000
optimizer_index_cost_adj.......... 100
These parameters are correct?
How can I undestanding which are the parameters correct with this hardware??
Raf
-
Originally posted by gbrabham
Your SGA has become fragmented ...
Your will have to restart the database to clear up the fragmentation.
Not necessary to restart - flushing the shared pool will do the trick.
Vinit
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
|