The following errors pertaining to the compare config command are in the event_log:
11/05/07 19:11 Error: Oracle Error 4031: ORA-04031: unable to allocate
3896 bytes of shared memory ("shared pool","INSERT INTO
SHAREPLEX_DATAEQ...","sga heap(1,0)","kglsim object batch").
[sp_desvr(deq)/22478]
11/05/07 19:11 Internal error: sp_declt did not connect back for table
"<owner>"."<tablename>", going on to next table if any
[sp_desvr(deq)/22478]
.
.
.
The "show compare" also showed that it failed on all the tables in config:
22478.1 "TTE"."PLAN_TABLE" Error 05-Nov-07
17:10:44
22478.2 "TTE"."T_TTE_ACCT" Error 05-Nov-07
17:10:44
22478.3 "TTE"."T_TTE_APP_LOG" Error 05-Nov-07
17:10:44
22478.4 "TTE"."T_TTE_IM_COMPANY" Error 05-Nov-07
17:10:44
22478.5 "TTE"."T_TTE_IM_USER" Error 05-Nov-07
17:10:44
22478.6 "TTE"."T_TTE_IO" Error 05-Nov-07
17:10:44
22478.7 "TTE"."T_TTE_JOB_STATUS" Error 05-Nov-07
17:10:44
22478.8 "TTE"."T_TTE_REF_HOLIDAY" Error 05-Nov-07
17:10:44
Fragmentation in Shared Pool may be causing the error
Resolution:
1. The first resolution is to shutdown Shareplex and increase the value of the init.ora parameter "shared_pool_size" accordingly.
Workaround:
1. Avoid using compare config and use compare at individual table level. Running compare this way is less resource intensive. One can run 3-5 compare concurrently to make it faster.
Error: ORA 4031
Text: unable to allocate %s bytes of shared memory (%s,%s,%s)
-------------------------------------------------------------------------------
Cause: More shared memory is needed than was allocated in the shared pool.
Action: 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
init.ora parameter "shared_pool_size".
Please see Oracle's Metalink document Doc ID Note:396940.1 titled "Troubleshooting and Diagnosing ORA-4031 Error" for more information regarding this Oracle error.
