Core dump when running multiple row insert from select

[notbob]

We are running Solaris Intel with 11.2.0.2
This is a new oracle setup for us.
When running an insert from test table1 with 1 column(id), 2 rows(JT,TJ) into test table2 with 1 column(id) we get a core dump.

Further investigation shows that if we run a single row insert from select, it runs fine.

Could this be a buffer setting we need to adjust or ???
At this point, we are out of ideas to try.

What works
Insert into table2 (id) select id from table1 where rownum<2

What core dumps
Insert into table2 (id) select id from table1

We have narrowed it down via other test tables, etc... that it is not a permission issue and is only core dumping when multiple rows are being selected.

[gandolf989]

I would look to see what triggers exist for this table. You might also look for
invalid objects in the database, Make sure that your SGA is large enough, etc.
But you may also want to post any trace files that you have.

[notbob]

No triggers as it is just 2 standard tables I created for testing.
Not seeing any invalid objects and trace files aren't really producing anything that has any hint of being useful.
I will check the setting size.
Do you have a recommended size I should try?

[gandolf989]

With 11gR2, I have found the the shared pool needs to be at least 768MB, and db block buffers should be at least 32MB, probably more. If I had to guess I would say that you are using automatic memory management and you set the size too small. 11gR2 should have at least 1GB for memory_max_target and memory_target. 2GB would be better. I would also set them to the same value. But for the most part I specify manually sga_target, sga_max_size, db_cache_size, pga_aggregate_target, java_pool_size, and large_pool_size, leaving memory_max_target and memory_target set to 0.

[notbob]

Thank you for this information!
Our block buffers were 0 and we have set them to 32mb.
We were using auto memory, but have switched so we can play with the parameters.
The mem_max_target and mem_target are at 5gb ea.
I would be curious to see what we should set the other parameters to that you listed?
I have spooled our parameters to a text file.
If it would be helpful, I could send that to you if you wanted to take a look at them?
Appreciate all your help as we are lost on this one.

[gandolf989]

You should be able to just post the parameters online.
Try this query:

column name format a50
column value format a30

select name, to_char(value, '999,999,999,999') bytes from v$parameter where type=6 order by 1;

This query will just show the memory parameters.

[notbob]

Thank you!
Here are the results.

client_result_cache_lag                 3,000   
client_result_cache_size                    0
db_16k_cache_size                           0
db_2k_cache_size                            0
db_32k_cache_size                           0
db_4k_cache_size                            0
db_8k_cache_size                            0
db_cache_size                      50,331,648   
db_flash_cache_size                         0   
db_keep_cache_size                          0   
db_recovery_file_dest_size    107,374,182,400   
db_recycle_cache_size                       0   
java_pool_size                     50,331,648   
large_pool_size                    50,331,648   
memory_max_target               5,368,709,120   
memory_target                   5,368,709,120   
olap_page_pool_size                         0   
pga_aggregate_target              629,145,600   
result_cache_max_size               8,388,608   
sga_max_size                    5,368,709,120   
sga_target                      3,221,225,472   
shared_pool_reserved_size          41,943,040   
shared_pool_size                  838,860,800   
streams_pool_size                 536,870,912

[gandolf989]

You might try turning off managed memory and also did you follow the
before you install guide to verify that the prerequisites are all set up?
I also edited your post to make it more readable.

[hany]

We are running Solaris Intel with 11.2.0.2
This is a new oracle setup for us.
When running an insert from test table1 with 1 column(id), 2 rows(JT,TJ) into test table2 with 1 column(id) we get a core dump.

Further investigation shows that if we run a single row insert from select, it runs fine.

Could this be a buffer setting we need to adjust or ???
At this point, we are out of ideas to try.

What works
Insert into table2 (id) select id from table1 where rownum<2

What core dumps
Insert into table2 (id) select id from table1

We have narrowed it down via other test tables, etc... that it is not a permission issue and is only core dumping when multiple rows are being selected.

Hi,

Are you only seeing core dumps? No ORA-7445 or other errors in the alert log file?
You may need to use a debugging tool like gdb to extract the call stack and look for similar bugs in MyOracleSupport.

Hope this helps.