My Oracle Support Banner

SMS Screens Doesn't Use Bind Variables when Querying Subscriber EDRs and Database Shared Pool is Overflow with Error "Ora-04031: Unable to Allocate Xxx Bytes of Shared Memory" (Doc ID 1440442.1)

Last updated on MARCH 18, 2018

Applies to:

Oracle Communications Network Charging and Control - Version 4.4.0 and later
Information in this document applies to any platform.
***Checked for relevance on 27-Oct-2013***


  1. PImanager, ccsProfileDaemon, smsMaster and ccsPeriodicCharge ...etc.
  2. Replication events were accumulated and not sent to the replication client nodes.
  3. Replication Jobs execution failed and were marked as broken.

On the impacted SMS node (These commands should be run from the SMS. If the SMS is a cluster, run these commands from only impacted node):

su - oracle
oracle@SMS>cd /u01/app/oracle/admin/SMF/bdump/
oracle@SMS>more alert_SMF.log

Sun Mar 11 05:34:54 2012
Errors in file /u01/app/oracle/admin/SMF1/bdump/smf1_j001_16605.trc:
ORA-12012: error on auto execute of job 1081
ORA-04031: unable to allocate 576 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","sql area","ckydef : kkdlcky")
ORA-06512: at "SMF.REP_CHECK", line 28
ORA-06512: at line 1
Sun Mar 11 05:34:59 2012
Errors in file /u01/app/oracle/admin/SMF1/bdump/smf1_j000_16540.trc:
ORA-12012: error on auto execute of job 2736
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","Typecheck","chedef : qcuatc")
ORA-20011: RENUMBER_EVENTS: Error renumbering :-
ORA-06512: at "SMF.REP_INTERNAL", line 329
ORA-06512: at line 1


su - oracle
oracle@SMS>sqlplus " / as sysdba"

SQL>col WHAT format a44;
SQL>col LOG_USER format a10
SQL>col interval format a40

SQL>select job,log_user,last_date, next_date,what,FAILURES,instance,broken from dba_jobs where WHAT like '%SMF.%';

JOB LOG_USER      NEXT_DATE                      WHAT                   FAILURES  INSTANCE BROKEN
---- ---------- ------------- ------------------------------------------ ------- ---------- ------
172 SYSTEM      40000101000000 SMF.REP_INTERNAL.FLUSH_REP_ORA_RENUMBERED; 17         1        Y
175 SYSTEM      40000101000000 SMF.REP_INTERNAL.RENUMBER_EVENTS;          17         2        Y
169 SYSTEM      40000101000000 SMF.REP_INTERNAL.FLUSH_REP_ORA_RENUMBERED; 17         2        Y
196 SYSTEM      40000101000000 SMF.JOBSTATUSCHECK;                        17         0        N
170 SYSTEM      40000101000000 SMF.REP_INTERNAL.RENUMBER_EVENTS;          17         1        Y
# su - smf_oper
# sqlplus smf/(password)
SQL> select count(*) from rep_ora_event;

SQL> select count(*) from rep_ora_renumbered;


Value of "xxxx" will keep increasing as the replication events are not sent to replication clients.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.