OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » ORA-4031 (ORA-04031: unable to allocate 4024 bytes of shared memory )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-4031 [message #655806]

Tue, 13 September 2016 03:57

pcouas
Messages: 112
Registered: February 2016

Senior Member

Hi,

My Customer on Oracle10XE has following error message

Code: [Select all] [Show/ hide]

ORA-04031: unable to allocate 4024 bytes of shared memory ("large pool","unknown object","kxs-heap-w","kokeglb: kokegPinLob")

Oracle parameters give me

Code: [Select all] [Show/ hide]

NAME                              TYPE        VALUE                                                                
--------------------------------- ----------- -------------------------------------------------------------------- 
active_instance_count             integer                                                                          
aq_tm_processes                   integer     0                                                                    
archive_lag_target                integer     0                                                                    
asm_diskgroups                    string                                                                           
asm_diskstring                    string                                                                           
asm_power_limit                   integer     1                                                                    
audit_file_dest                   string      /usr/lib/oracle/xe/app/oracle/admin/XE/adump                         
audit_syslog_level                string                                                                           
audit_sys_operations              boolean     FALSE                                                                
audit_trail                       string      NONE                                                                 
background_core_dump              string      partial                                                              
background_dump_dest              string      /usr/lib/oracle/xe/app/oracle/admin/XE/bdump                         
backup_tape_io_slaves             boolean     FALSE                                                                
bitmap_merge_area_size            integer     1048576                                                              
blank_trimming                    boolean     FALSE                                                                
buffer_pool_keep                  string                                                                           
buffer_pool_recycle               string                                                                           
circuits                          integer                                                                          
cluster_database                  boolean     FALSE                                                                
cluster_database_instances        integer     1                                                                    
cluster_interconnects             string                                                                           
commit_point_strength             integer     1                                                                    
commit_write                      string                                                                           
compatible                        string      10.2.0.1.0                                                           
control_file_record_keep_time     integer     7                                                                    
control_files                     string      /usr/lib/oracle/xe/oradata/XE/control.dbf                            
core_dump_dest                    string      /usr/lib/oracle/xe/app/oracle/admin/XE/cdump                         
cpu_count                         integer     1                                                                    
create_bitmap_area_size           integer     8388608                                                              
create_stored_outlines            string                                                                           
cursor_sharing                    string      EXACT                                                                
cursor_space_for_time             boolean     FALSE                                                                
db_block_buffers                  integer     0                                                                    
db_block_checking                 string      FALSE                                                                
db_block_checksum                 string      TRUE                                                                 
db_block_size                     integer     8192                                                                 
db_cache_advice                   string      ON                                                                   
db_cache_size                     big integer 0                                                                    
db_create_file_dest               string                                                                           
db_create_online_log_dest_1       string                                                                           
db_create_online_log_dest_2       string                                                                           
db_create_online_log_dest_3       string                                                                           
db_create_online_log_dest_4       string                                                                           
db_create_online_log_dest_5       string                                                                           
db_domain                         string                                                                           
db_file_multiblock_read_count     integer     7                                                                    
db_file_name_convert              string                                                                           
db_files                          integer     200                                                                  
db_flashback_retention_target     integer     1440                                                                 
db_keep_cache_size                big integer 0                                                                    
db_name                           string      XE                                                                   
db_recovery_file_dest             string      /usr/lib/oracle/xe/app/oracle/flash_recovery_area                    
db_recovery_file_dest_size        big integer 10G                                                                  
db_recycle_cache_size             big integer 0                                                                    
db_unique_name                    string      XE                                                                   
dbwr_io_slaves                    integer     0                                                                    
db_writer_processes               integer     1                                                                    
db_16k_cache_size                 big integer 0                                                                    
db_2k_cache_size                  big integer 0                                                                    
db_32k_cache_size                 big integer 0                                                                    
db_4k_cache_size                  big integer 0                                                                    
db_8k_cache_size                  big integer 0                                                                    
ddl_wait_for_locks                boolean     FALSE                                                                
dg_broker_config_file1            string      /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/dr1XE.dat    
dg_broker_config_file2            string      /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/dr2XE.dat    
dg_broker_start                   boolean     FALSE                                                                
disk_asynch_io                    boolean     TRUE                                                                 
dispatchers                       string      (PROTOCOL=TCP)(DISPATCHERS=1)                                        
distributed_lock_timeout          integer     60                                                                   
dml_locks                         integer     1100                                                                 
drs_start                         boolean     FALSE                                                                
event                             string                                                                           
fal_client                        string                                                                           
fal_server                        string                                                                           
fast_start_io_target              integer     0                                                                    
fast_start_mttr_target            integer     0                                                                    
fast_start_parallel_rollback      string      LOW                                                                  
fileio_network_adapters           string                                                                           
file_mapping                      boolean     FALSE                                                                
filesystemio_options              string      none                                                                 
fixed_date                        string                                                                           
gc_files_to_locks                 string                                                                           
gcs_server_processes              integer     0                                                                    
global_context_pool_size          string                                                                           
global_names                      boolean     FALSE                                                                
hash_area_size                    integer     131072                                                               
hi_shared_memory_address          integer     0                                                                    
hs_autoregister                   boolean     TRUE                                                                 
ifile                             file                                                                             
instance_groups                   string                                                                           
instance_name                     string      XE                                                                   
instance_number                   integer     0                                                                    
instance_type                     string      RDBMS                                                                
java_max_sessionspace_size        integer     0                                                                    
java_pool_size                    big integer 0                                                                    
java_soft_sessionspace_limit      integer     0                                                                    
job_queue_processes               integer     20                                                                   
_kgl_large_heap_warning_threshold integer     52428800                                                             
large_pool_size                   big integer 0                                                                    
ldap_directory_access             string      NONE                                                                 
license_max_sessions              integer     0                                                                    
license_max_users                 integer     0                                                                    
license_sessions_warning          integer     0                                                                    
local_listener                    string                                                                           
lock_name_space                   string                                                                           
lock_sga                          boolean     FALSE                                                                
log_archive_config                string                                                                           
log_archive_dest                  string                                                                           
log_archive_dest_state_1          string      enable                                                               
log_archive_dest_state_10         string      enable                                                               
log_archive_dest_state_2          string      enable                                                               
log_archive_dest_state_3          string      enable                                                               
log_archive_dest_state_4          string      enable                                                               
log_archive_dest_state_5          string      enable                                                               
log_archive_dest_state_6          string      enable                                                               
log_archive_dest_state_7          string      enable                                                               
log_archive_dest_state_8          string      enable                                                               
log_archive_dest_state_9          string      enable                                                               
log_archive_dest_1                string                                                                           
log_archive_dest_10               string                                                                           
log_archive_dest_2                string                                                                           
log_archive_dest_3                string                                                                           
log_archive_dest_4                string                                                                           
log_archive_dest_5                string                                                                           
log_archive_dest_6                string                                                                           
log_archive_dest_7                string                                                                           
log_archive_dest_8                string                                                                           
log_archive_dest_9                string                                                                           
log_archive_duplex_dest           string                                                                           
log_archive_format                string      %t_%s_%r.dbf                                                         
log_archive_local_first           boolean     TRUE                                                                 
log_archive_max_processes         integer     2                                                                    
log_archive_min_succeed_dest      integer     1                                                                    
log_archive_start                 boolean     FALSE                                                                
log_archive_trace                 integer     0                                                                    
log_buffer                        integer     2887168                                                              
log_checkpoint_interval           integer     0                                                                    
log_checkpoints_to_alert          boolean     FALSE                                                                
log_checkpoint_timeout            integer     1800                                                                 
log_file_name_convert             string                                                                           
logmnr_max_persistent_sessions    integer     1                                                                    
max_commit_propagation_delay      integer     0                                                                    
max_dispatchers                   integer                                                                          
max_dump_file_size                string      UNLIMITED                                                            
max_enabled_roles                 integer     150                                                                  
max_shared_servers                integer                                                                          
nls_calendar                      string                                                                           
nls_comp                          string                                                                           
nls_currency                      string                                                                           
nls_date_format                   string                                                                           
nls_date_language                 string                                                                           
nls_dual_currency                 string                                                                           
nls_iso_currency                  string                                                                           
nls_language                      string      AMERICAN                                                             
nls_length_semantics              string      BYTE                                                                 
nls_nchar_conv_excp               string      FALSE                                                                
nls_numeric_characters            string                                                                           
nls_sort                          string                                                                           
nls_territory                     string      AMERICA                                                              
nls_time_format                   string                                                                           
nls_timestamp_format              string                                                                           
nls_timestamp_tz_format           string                                                                           
nls_time_tz_format                string                                                                           
object_cache_max_size_percent     integer     10                                                                   
object_cache_optimal_size         integer     102400                                                               
olap_page_pool_size               big integer 0                                                                    
open_cursors                      integer     300                                                                  
open_links                        integer     4                                                                    
open_links_per_instance           integer     4                                                                    
optimizer_dynamic_sampling        integer     2                                                                    
optimizer_features_enable         string      10.2.0.1                                                             
optimizer_index_caching           integer     0                                                                    
optimizer_index_cost_adj          integer     100                                                                  
optimizer_mode                    string      ALL_ROWS                                                             
optimizer_secure_view_merging     boolean     TRUE                                                                 
os_authent_prefix                 string                                                                           
os_roles                          boolean     FALSE                                                                
O7_DICTIONARY_ACCESSIBILITY       boolean     FALSE                                                                
parallel_adaptive_multi_user      boolean     TRUE                                                                 
parallel_automatic_tuning         boolean     FALSE                                                                
parallel_execution_message_size   integer     2148                                                                 
parallel_instance_group           string                                                                           
parallel_max_servers              integer     0                                                                    
parallel_min_percent              integer     0                                                                    
parallel_min_servers              integer     0                                                                    
parallel_server                   boolean     FALSE                                                                
parallel_server_instances         integer     1                                                                    
parallel_threads_per_cpu          integer     2                                                                    
pga_aggregate_target              big integer 160M                                                                 
plsql_ccflags                     string                                                                           
plsql_code_type                   string      INTERPRETED                                                          
plsql_compiler_flags              string      INTERPRETED, NON_DEBUG                                               
plsql_debug                       boolean     FALSE                                                                
plsql_native_library_dir          string                                                                           
plsql_native_library_subdir_count integer     0                                                                    
plsql_optimize_level              integer     2                                                                    
plsql_v2_compatibility            boolean     FALSE                                                                
plsql_warnings                    string      DISABLE:ALL                                                          
pre_page_sga                      boolean     FALSE                                                                
processes                         integer     200                                                                  
query_rewrite_enabled             string      TRUE                                                                 
query_rewrite_integrity           string      enforced                                                             
rdbms_server_dn                   string                                                                           
read_only_open_delayed            boolean     FALSE                                                                
recovery_parallelism              integer     0                                                                    
recyclebin                        string      on                                                                   
remote_archive_enable             string      true                                                                 
remote_dependencies_mode          string      TIMESTAMP                                                            
remote_listener                   string                                                                           
remote_login_passwordfile         string      EXCLUSIVE                                                            
remote_os_authent                 boolean     FALSE                                                                
remote_os_roles                   boolean     FALSE                                                                
replication_dependency_tracking   boolean     TRUE                                                                 
resource_limit                    boolean     FALSE                                                                
resource_manager_plan             string                                                                           
resumable_timeout                 integer     0                                                                    
rollback_segments                 string                                                                           
serial_reuse                      string      disable                                                              
service_names                     string      XE                                                                   
session_cached_cursors            integer     20                                                                   
session_max_open_files            integer     10                                                                   
sessions                          integer     250                                                                  
sga_max_size                      big integer 480M                                                                 
sga_target                        big integer 480M                                                                 
shadow_core_dump                  string      partial                                                              
shared_memory_address             integer     0                                                                    
shared_pool_reserved_size         big integer 6920601                                                              
shared_pool_size                  big integer 0                                                                    
shared_servers                    integer     4                                                                    
shared_server_sessions            integer                                                                          
skip_unusable_indexes             boolean     TRUE                                                                 
smtp_out_server                   string                                                                           
sort_area_retained_size           integer     0                                                                    
sort_area_size                    integer     65536                                                                
spfile                            string      /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/spfileXE.ora 
sql_trace                         boolean     FALSE                                                                
sqltune_category                  string      DEFAULT                                                              
sql_version                       string      NATIVE                                                               
sql92_security                    boolean     FALSE                                                                
standby_archive_dest              string      ?/dbs/arch                                                           
standby_file_management           string      MANUAL                                                               
star_transformation_enabled       string      FALSE                                                                
statistics_level                  string      TYPICAL                                                              
streams_pool_size                 big integer 0                                                                    
tape_asynch_io                    boolean     TRUE                                                                 
thread                            integer     0                                                                    
timed_os_statistics               integer     0                                                                    
timed_statistics                  boolean     TRUE                                                                 
trace_enabled                     boolean     TRUE                                                                 
tracefile_identifier              string                                                                           
transactions                      integer     275                                                                  
transactions_per_rollback_segment integer     5                                                                    
undo_management                   string      AUTO                                                                 
undo_retention                    integer     900                                                                  
undo_tablespace                   string      UNDO                                                                 
use_indirect_data_buffers         boolean     FALSE                                                                
user_dump_dest                    string      /usr/lib/oracle/xe/app/oracle/admin/XE/udump                         
utl_file_dir                      string                                                                           
workarea_size_policy              string      AUTO

How could i increase memory for avoiding this error message ?

Regards
Phil

Report message to a moderator

Re: ORA-4031 [message #655807 is a reply to message #655806]

Tue, 13 September 2016 03:59

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The message is clear: enlarge your large pool.

Report message to a moderator

Re: ORA-4031 [message #655808 is a reply to message #655806]

Tue, 13 September 2016 04:04

John Watson
Messages: 8977
Registered: January 2010
Location: Global Village

Senior Member

Probably easiest to raise your sga_target, and let Uncle Oracle do the pool allocations.

Report message to a moderator

Re: ORA-4031 [message #655809 is a reply to message #655808]

Tue, 13 September 2016 04:17

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Quote:

shared_pool_size big integer 0

What does the following yield?

Code: [Select all] [Show/ hide]

SQL> show sga

Report message to a moderator

Re: ORA-4031 [message #655810 is a reply to message #655809]

Tue, 13 September 2016 04:24

pcouas
Messages: 112
Registered: February 2016

Senior Member

show sga

Code: [Select all] [Show/ hide]

NAME                              TYPE        VALUE                                                                
--------------------------------- ----------- -------------------------------------------------------------------- 
active_instance_count             integer                                                                          
aq_tm_processes                   integer     0                                                                    
archive_lag_target                integer     0                                                                    
asm_diskgroups                    string                                                                           
asm_diskstring                    string                                                                           
asm_power_limit                   integer     1                                                                    
audit_file_dest                   string      /usr/lib/oracle/xe/app/oracle/admin/XE/adump                         
audit_syslog_level                string                                                                           
audit_sys_operations              boolean     FALSE                                                                
audit_trail                       string      NONE                                                                 
background_core_dump              string      partial                                                              
background_dump_dest              string      /usr/lib/oracle/xe/app/oracle/admin/XE/bdump                         
backup_tape_io_slaves             boolean     FALSE                                                                
bitmap_merge_area_size            integer     1048576                                                              
blank_trimming                    boolean     FALSE                                                                
buffer_pool_keep                  string                                                                           
buffer_pool_recycle               string                                                                           
circuits                          integer                                                                          
cluster_database                  boolean     FALSE                                                                
cluster_database_instances        integer     1                                                                    
cluster_interconnects             string                                                                           
commit_point_strength             integer     1                                                                    
commit_write                      string                                                                           
compatible                        string      10.2.0.1.0                                                           
control_file_record_keep_time     integer     7                                                                    
control_files                     string      /usr/lib/oracle/xe/oradata/XE/control.dbf                            
core_dump_dest                    string      /usr/lib/oracle/xe/app/oracle/admin/XE/cdump                         
cpu_count                         integer     1                                                                    
create_bitmap_area_size           integer     8388608                                                              
create_stored_outlines            string                                                                           
cursor_sharing                    string      EXACT                                                                
cursor_space_for_time             boolean     FALSE                                                                
db_block_buffers                  integer     0                                                                    
db_block_checking                 string      FALSE                                                                
db_block_checksum                 string      TRUE                                                                 
db_block_size                     integer     8192                                                                 
db_cache_advice                   string      ON                                                                   
db_cache_size                     big integer 0                                                                    
db_create_file_dest               string                                                                           
db_create_online_log_dest_1       string                                                                           
db_create_online_log_dest_2       string                                                                           
db_create_online_log_dest_3       string                                                                           
db_create_online_log_dest_4       string                                                                           
db_create_online_log_dest_5       string                                                                           
db_domain                         string                                                                           
db_file_multiblock_read_count     integer     7                                                                    
db_file_name_convert              string                                                                           
db_files                          integer     200                                                                  
db_flashback_retention_target     integer     1440                                                                 
db_keep_cache_size                big integer 0                                                                    
db_name                           string      XE                                                                   
db_recovery_file_dest             string      /usr/lib/oracle/xe/app/oracle/flash_recovery_area                    
db_recovery_file_dest_size        big integer 10G                                                                  
db_recycle_cache_size             big integer 0                                                                    
db_unique_name                    string      XE                                                                   
dbwr_io_slaves                    integer     0                                                                    
db_writer_processes               integer     1                                                                    
db_16k_cache_size                 big integer 0                                                                    
db_2k_cache_size                  big integer 0                                                                    
db_32k_cache_size                 big integer 0                                                                    
db_4k_cache_size                  big integer 0                                                                    
db_8k_cache_size                  big integer 0                                                                    
ddl_wait_for_locks                boolean     FALSE                                                                
dg_broker_config_file1            string      /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/dr1XE.dat    
dg_broker_config_file2            string      /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/dr2XE.dat    
dg_broker_start                   boolean     FALSE                                                                
disk_asynch_io                    boolean     TRUE                                                                 
dispatchers                       string      (PROTOCOL=TCP)(DISPATCHERS=1)                                        
distributed_lock_timeout          integer     60                                                                   
dml_locks                         integer     1100                                                                 
drs_start                         boolean     FALSE                                                                
event                             string                                                                           
fal_client                        string                                                                           
fal_server                        string                                                                           
fast_start_io_target              integer     0                                                                    
fast_start_mttr_target            integer     0                                                                    
fast_start_parallel_rollback      string      LOW                                                                  
fileio_network_adapters           string                                                                           
file_mapping                      boolean     FALSE                                                                
filesystemio_options              string      none                                                                 
fixed_date                        string                                                                           
gc_files_to_locks                 string                                                                           
gcs_server_processes              integer     0                                                                    
global_context_pool_size          string                                                                           
global_names                      boolean     FALSE                                                                
hash_area_size                    integer     131072                                                               
hi_shared_memory_address          integer     0                                                                    
hs_autoregister                   boolean     TRUE                                                                 
ifile                             file                                                                             
instance_groups                   string                                                                           
instance_name                     string      XE                                                                   
instance_number                   integer     0                                                                    
instance_type                     string      RDBMS                                                                
java_max_sessionspace_size        integer     0                                                                    
java_pool_size                    big integer 0                                                                    
java_soft_sessionspace_limit      integer     0                                                                    
job_queue_processes               integer     20                                                                   
_kgl_large_heap_warning_threshold integer     52428800                                                             
large_pool_size                   big integer 0                                                                    
ldap_directory_access             string      NONE                                                                 
license_max_sessions              integer     0                                                                    
license_max_users                 integer     0                                                                    
license_sessions_warning          integer     0                                                                    
local_listener                    string                                                                           
lock_name_space                   string                                                                           
lock_sga                          boolean     FALSE                                                                
log_archive_config                string                                                                           
log_archive_dest                  string                                                                           
log_archive_dest_state_1          string      enable                                                               
log_archive_dest_state_10         string      enable                                                               
log_archive_dest_state_2          string      enable                                                               
log_archive_dest_state_3          string      enable                                                               
log_archive_dest_state_4          string      enable                                                               
log_archive_dest_state_5          string      enable                                                               
log_archive_dest_state_6          string      enable                                                               
log_archive_dest_state_7          string      enable                                                               
log_archive_dest_state_8          string      enable                                                               
log_archive_dest_state_9          string      enable                                                               
log_archive_dest_1                string                                                                           
log_archive_dest_10               string                                                                           
log_archive_dest_2                string                                                                           
log_archive_dest_3                string                                                                           
log_archive_dest_4                string                                                                           
log_archive_dest_5                string                                                                           
log_archive_dest_6                string                                                                           
log_archive_dest_7                string                                                                           
log_archive_dest_8                string                                                                           
log_archive_dest_9                string                                                                           
log_archive_duplex_dest           string                                                                           
log_archive_format                string      %t_%s_%r.dbf                                                         
log_archive_local_first           boolean     TRUE                                                                 
log_archive_max_processes         integer     2                                                                    
log_archive_min_succeed_dest      integer     1                                                                    
log_archive_start                 boolean     FALSE                                                                
log_archive_trace                 integer     0                                                                    
log_buffer                        integer     2887168                                                              
log_checkpoint_interval           integer     0                                                                    
log_checkpoints_to_alert          boolean     FALSE                                                                
log_checkpoint_timeout            integer     1800                                                                 
log_file_name_convert             string                                                                           
logmnr_max_persistent_sessions    integer     1                                                                    
max_commit_propagation_delay      integer     0                                                                    
max_dispatchers                   integer                                                                          
max_dump_file_size                string      UNLIMITED                                                            
max_enabled_roles                 integer     150                                                                  
max_shared_servers                integer                                                                          
nls_calendar                      string                                                                           
nls_comp                          string                                                                           
nls_currency                      string                                                                           
nls_date_format                   string                                                                           
nls_date_language                 string                                                                           
nls_dual_currency                 string                                                                           
nls_iso_currency                  string                                                                           
nls_language                      string      AMERICAN                                                             
nls_length_semantics              string      BYTE                                                                 
nls_nchar_conv_excp               string      FALSE                                                                
nls_numeric_characters            string                                                                           
nls_sort                          string                                                                           
nls_territory                     string      AMERICA                                                              
nls_time_format                   string                                                                           
nls_timestamp_format              string                                                                           
nls_timestamp_tz_format           string                                                                           
nls_time_tz_format                string                                                                           
object_cache_max_size_percent     integer     10                                                                   
object_cache_optimal_size         integer     102400                                                               
olap_page_pool_size               big integer 0                                                                    
open_cursors                      integer     300                                                                  
open_links                        integer     4                                                                    
open_links_per_instance           integer     4                                                                    
optimizer_dynamic_sampling        integer     2                                                                    
optimizer_features_enable         string      10.2.0.1                                                             
optimizer_index_caching           integer     0                                                                    
optimizer_index_cost_adj          integer     100                                                                  
optimizer_mode                    string      ALL_ROWS                                                             
optimizer_secure_view_merging     boolean     TRUE                                                                 
os_authent_prefix                 string                                                                           
os_roles                          boolean     FALSE                                                                
O7_DICTIONARY_ACCESSIBILITY       boolean     FALSE                                                                
parallel_adaptive_multi_user      boolean     TRUE                                                                 
parallel_automatic_tuning         boolean     FALSE                                                                
parallel_execution_message_size   integer     2148                                                                 
parallel_instance_group           string                                                                           
parallel_max_servers              integer     0                                                                    
parallel_min_percent              integer     0                                                                    
parallel_min_servers              integer     0                                                                    
parallel_server                   boolean     FALSE                                                                
parallel_server_instances         integer     1                                                                    
parallel_threads_per_cpu          integer     2                                                                    
pga_aggregate_target              big integer 160M                                                                 
plsql_ccflags                     string                                                                           
plsql_code_type                   string      INTERPRETED                                                          
plsql_compiler_flags              string      INTERPRETED, NON_DEBUG                                               
plsql_debug                       boolean     FALSE                                                                
plsql_native_library_dir          string                                                                           
plsql_native_library_subdir_count integer     0                                                                    
plsql_optimize_level              integer     2                                                                    
plsql_v2_compatibility            boolean     FALSE                                                                
plsql_warnings                    string      DISABLE:ALL                                                          
pre_page_sga                      boolean     FALSE                                                                
processes                         integer     200                                                                  
query_rewrite_enabled             string      TRUE                                                                 
query_rewrite_integrity           string      enforced                                                             
rdbms_server_dn                   string                                                                           
read_only_open_delayed            boolean     FALSE                                                                
recovery_parallelism              integer     0                                                                    
recyclebin                        string      on                                                                   
remote_archive_enable             string      true                                                                 
remote_dependencies_mode          string      TIMESTAMP                                                            
remote_listener                   string                                                                           
remote_login_passwordfile         string      EXCLUSIVE                                                            
remote_os_authent                 boolean     FALSE                                                                
remote_os_roles                   boolean     FALSE                                                                
replication_dependency_tracking   boolean     TRUE                                                                 
resource_limit                    boolean     FALSE                                                                
resource_manager_plan             string                                                                           
resumable_timeout                 integer     0                                                                    
rollback_segments                 string                                                                           
serial_reuse                      string      disable                                                              
service_names                     string      XE                                                                   
session_cached_cursors            integer     20                                                                   
session_max_open_files            integer     10                                                                   
sessions                          integer     250                                                                  
sga_max_size                      big integer 480M                                                                 
sga_target                        big integer 480M                                                                 
shadow_core_dump                  string      partial                                                              
shared_memory_address             integer     0                                                                    
shared_pool_reserved_size         big integer 6920601                                                              
shared_pool_size                  big integer 0                                                                    
shared_servers                    integer     4                                                                    
shared_server_sessions            integer                                                                          
skip_unusable_indexes             boolean     TRUE                                                                 
smtp_out_server                   string                                                                           
sort_area_retained_size           integer     0                                                                    
sort_area_size                    integer     65536                                                                
spfile                            string      /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/spfileXE.ora 
sql_trace                         boolean     FALSE                                                                
sqltune_category                  string      DEFAULT                                                              
sql_version                       string      NATIVE                                                               
sql92_security                    boolean     FALSE                                                                
standby_archive_dest              string      ?/dbs/arch                                                           
standby_file_management           string      MANUAL                                                               
star_transformation_enabled       string      FALSE                                                                
statistics_level                  string      TYPICAL                                                              
streams_pool_size                 big integer 0                                                                    
tape_asynch_io                    boolean     TRUE                                                                 
thread                            integer     0                                                                    
timed_os_statistics               integer     0                                                                    
timed_statistics                  boolean     TRUE                                                                 
trace_enabled                     boolean     TRUE                                                                 
tracefile_identifier              string                                                                           
transactions                      integer     275                                                                  
transactions_per_rollback_segment integer     5                                                                    
undo_management                   string      AUTO                                                                 
undo_retention                    integer     900                                                                  
undo_tablespace                   string      UNDO                                                                 
use_indirect_data_buffers         boolean     FALSE                                                                
user_dump_dest                    string      /usr/lib/oracle/xe/app/oracle/admin/XE/udump                         
utl_file_dir                      string                                                                           
workarea_size_policy              string      AUTO                                                                 
Total System Global Area   503316480 bytes      
Fixed Size                   1259712 bytes      
Variable Size              482346816 bytes      
Database Buffers            16777216 bytes      
Redo Buffers                 2932736 bytes

Report message to a moderator

Re: ORA-4031 [message #655811 is a reply to message #655810]

Tue, 13 September 2016 04:29

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

That is not the output from a "SHOW SGA" command. For example, on my Oracle XE system:

Code: [Select all] [Show/ hide]

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show sga

Total System Global Area 1068937216 bytes
Fixed Size                  2260048 bytes
Variable Size             679478192 bytes
Database Buffers          381681664 bytes
Redo Buffers                5517312 bytes
SQL>

Report message to a moderator

Re: ORA-4031 [message #655812 is a reply to message #655810]

Tue, 13 September 2016 04:34

pcouas
Messages: 112
Registered: February 2016

Senior Member

Sorry
show sga;

Code: [Select all] [Show/ hide]

Total System Global Area   503316480 bytes      
Fixed Size                   1259712 bytes      
Variable Size              482346816 bytes      
Database Buffers            16777216 bytes      
Redo Buffers                 2932736 bytes

Report message to a moderator

Re: ORA-4031 [message #655813 is a reply to message #655812]

Tue, 13 September 2016 04:46

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

OK, that shows your SGA is 480MB. How much RAM is on the server?

Report message to a moderator

Re: ORA-4031 [message #655815 is a reply to message #655813]

Tue, 13 September 2016 05:46

pcouas
Messages: 112
Registered: February 2016

Senior Member

1Go on Amazon EC2

Report message to a moderator

Re: ORA-4031 [message #655816 is a reply to message #655815]

Tue, 13 September 2016 06:06

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Far too small.

Report message to a moderator

Re: ORA-4031 [message #655817 is a reply to message #655815]

Tue, 13 September 2016 06:17

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Code: [Select all] [Show/ hide]

SQL> ALTER SYSTEM SET sga_target=1g SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Report message to a moderator

Re: ORA-4031 [message #655819 is a reply to message #655815]

Tue, 13 September 2016 11:08

bpeasland
Messages: 51
Registered: February 2015
Location: United States

Member

While 1GB is the minimum required for an Oracle installation, you may often need much more. As others have said, your Large Pool is too small and you need to increase its size. This may require you to obtain more memory for your host.

Cheers,
Brian

Report message to a moderator

Re: ORA-4031 [message #655837 is a reply to message #655819]

Wed, 14 September 2016 04:33

pcouas
Messages: 112
Registered: February 2016

Senior Member

Hi,

My Amazon EC2 instance as Memory 4Go
I have tried

SQL> ALTER SYSTEM SET sga_target=1g SCOPE=SPFILE; -- invalid parameter invalid
SQL> ALTER SYSTEM SET sga_target=900m SCOPE=SPFILE; --> i could not restart
SQL> ALTER SYSTEM SET sga_target=800m SCOPE=SPFILE; --> i could not restart instance, but PL/SQL query no effect

Report message to a moderator

Re: ORA-4031 [message #655838 is a reply to message #655837]

Wed, 14 September 2016 04:36

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Code: [Select all] [Show/ hide]

SQL> SHOW PARAMETER SGA

Report message to a moderator

Re: ORA-4031 [message #655839 is a reply to message #655837]

Wed, 14 September 2016 04:37

John Watson
Messages: 8977
Registered: January 2010
Location: Global Village

Senior Member

XE edition is restricted to 1GB, which (I think?) has to inclkude both SGA and PGA. So take it slowly. You know that sga_target=480m works, but not well. So try a bit bigger, perhaps 600m.

Report message to a moderator

Re: ORA-4031 [message #655840 is a reply to message #655837]

Wed, 14 September 2016 04:39

pcouas
Messages: 112
Registered: February 2016

Senior Member

Last test is
ALTER SYSTEM set sga_target=800m SCOPE=SPFILE;

Total System Global Area 838860800 bytes
Fixed Size 1261640 bytes
Variable Size 213913528 bytes
Database Buffers 620756992 bytes
Redo Buffers 2928640 bytes

Report message to a moderator

Re: ORA-4031 [message #657295 is a reply to message #655840]

Fri, 04 November 2016 10:48

naveen_rc2000@yahoo.com
Messages: 7
Registered: November 2016
Location: India

Junior Member

Hi All,

Even we have come across the situation may times.
However, it is not same edition /version.

My issue is I come across this issue daily at a particular time.
Upon my further diagnosis I found that this occurs during the Daily Backup Window.
Finally we understand that it requires high large pool during the Daily Backup using RMAN tool.

FYI this error can be diagnoised from the following Oracle Support Tool link:

Document 1088239.1 NOTE:1088239.1Title: Master Note for Diagnosing ORA-4031

Is there any way we configure such that this can be addressed.

Regards,
Naveen

Report message to a moderator

Re: ORA-4031 [message #657297 is a reply to message #657295]

Fri, 04 November 2016 11:07

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Is there any way we configure such that this can be addressed.

What "such" refers to?

Report message to a moderator

Re: ORA-4031 [message #657314 is a reply to message #657297]

Sat, 05 November 2016 12:27

naveen_rc2000@yahoo.com
Messages: 7
Registered: November 2016
Location: India

Junior Member

I mean if we can set large pool to dynamically increase during backup window.

Report message to a moderator

Re: ORA-4031 [message #657315 is a reply to message #657314]

Sat, 05 November 2016 12:34

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If this is a question, the answer is yes, see doc.
And:

Code: [Select all] [Show/ hide]

SQL> select ISSYS_MODIFIABLE from v$parameter where NAME='large_pool_size';
ISSYS_MOD
---------
IMMEDIATE

Report message to a moderator

Re: ORA-4031 [message #657328 is a reply to message #657315]

Sun, 06 November 2016 03:30

naveen_rc2000@yahoo.com
Messages: 7
Registered: November 2016
Location: India

Junior Member

This information is really useful.
Thanks you Michel.

Report message to a moderator

Previous Topic:	SYSAUX tablespace getting 99%
Next Topic:	Fatal NI connect error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 31 07:52:30 CDT 2025