ORA-04031 in export (Linux)
Michail Brzitwa
300MB tablespace and imported a 30MB full dump from a 7.3.4 db into it.
When trying to export the newly created scheme, exp exports all tables
etc. but when arriving at exporting the synonym it bombs out with:
. [...]
. exporting synonyms
EXP-00008: ORACLE error 4031 encountered
ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool",
"unknown object","joxlod: in ehe","ioc_allocate_pal")
EXP-00000: Export terminated unsuccessfully
Simple, I thought, and increased 'shared_pool_size' up to 64MB but it
didn't work (the kernel provides up to 64MB shared memory segments).
Strangely, the error message always says it's missing 4032 bytes, no
matter how small/big I set 'shared_pool_size'. I even tried to increase
'shared_pool_reserved_size' (after trying to find any hints in the
documentation and technet), or to flush the shared pool but it didn't
help. The db otherwise seems to work ok, the export being the only
connected client at that time.
I then dropped some public synonyms (not created by system users) where
TOAD also said 'unknown object type' or something, to no avail (it's
obviously a test database),
Tomorrow I'll try to set the shared pool size to some insane value, but
I have the feeling that the pool size is not the direct reason of this
error. Any ideas anyone?
Here are some relevant 'show parameters' values:
commit_point_strength integer 1
compatible string 8.1.0
control_file_record_keep_time integer 7
create_bitmap_area_size integer 8388608
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
db_block_buffers integer 4000
db_block_checking boolean FALSE
db_block_checksum boolean FALSE
db_block_lru_latches integer 1
db_block_max_dirty_target integer 4000
db_block_size integer 8192
db_domain string world
db_file_direct_io_count integer 64
db_file_multiblock_read_count integer 8
db_file_name_convert string
db_files integer 200
dblink_encrypt_login boolean FALSE
dbwr_io_slaves integer 0
db_writer_processes integer 1
disk_asynch_io boolean TRUE
distributed_transactions integer 10
dml_locks integer 264
enqueue_resources integer 484
hash_area_size integer 131072
hash_join_enabled boolean TRUE
hash_multiblock_io_count integer 0
hi_shared_memory_address integer 0
java_max_sessionspace_size integer 0
java_pool_size string 1024000
java_soft_sessionspace_limit integer 0
job_queue_interval integer 60
job_queue_processes integer 4
large_pool_size string 614400
lm_locks integer 12000
lm_ress integer 6000
lock_sga boolean FALSE
log_buffer integer 163840
log_checkpoint_interval integer 10000
log_checkpoints_to_alert boolean FALSE
log_checkpoint_timeout integer 1800
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
open_cursors integer 255
open_links integer 4
open_links_per_instance integer 4
optimizer_features_enable string 8.1.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 80000
optimizer_mode string CHOOSE
optimizer_percent_parallel integer 0
plsql_v2_compatibility boolean FALSE
pre_page_sga boolean FALSE
processes integer 50
row_locking string always
session_cached_cursors integer 0
session_max_open_files integer 10
sessions integer 60
shared_memory_address integer 0
shared_pool_reserved_size string 10000000
shared_pool_size string 35000000
sort_area_retained_size integer 65536
sort_area_size integer 65536
sort_multiblock_read_count integer 2
--
Michail Brzitwa <mic...@brzitwa.de> +49-511-343215
Michail Brzitwa
> . exporting synonyms
> EXP-00008: ORACLE error 4031 encountered
> ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool",
> "unknown object","joxlod: in ehe","ioc_allocate_pal")
> EXP-00000: Export terminated unsuccessfully
I found the reason:
> java_pool_size string 1024000
being too small for the export (no java involved in the actual tablespace
data). Setting the pool size to about 4M solved it. I don't know why a java
memory pool of this size is needed for my simple export.