ORA-04031: unable to allocate 384 bytes of shared memory

From: <kgsudhi_at_my-dejanews.com>
Date: Sun, 23 May 1999 09:47:30 GMT
Message-ID: <7i8iri$5l$1@nnrp1.deja.com>

Hi,

We are getting the following error message after 6-7 days of application running. Client programs are communicating with the database server processes via server mailboxes

ORA-04031: unable to allocate 384 bytes of shared memory ("select pos#,col# from
 icol$ ...","sql area","cursor execute memory")

ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SAMPLE_INS_TRG", line 12
ORA-04088: error during execution of trigger 'JEDLAB.SAMPLE_INS_TRG'
ORA-06512: at "SAMPLE_ADDSMP", line 437
ORA-06512: at line 1

$SEVERITY == "1"
$STATUS == "%X00000001"
We checked the V$LIBRARYCACHE and the following information were found.

SQL> select namespace,pins,reloads from v$librarycache;

NAMESPACE PINS RELOADS
--------------- ---------- ----------

SQL AREA           1170502      39262
TABLE/PROCEDURE     779529      23893
BODY                   123         78
TRIGGER               2229        923
INDEX                   21          0
CLUSTER                 15          0
OBJECT                   0          0
PIPE                     0          0

SQL> select * from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size                57652
Variable Size          23444656
Database Buffers       85196800
Redo Buffers             204800

SQL> select * from v$sgastat where name ='free memory';

NAME                            BYTES
-------------------------- ----------
free memory                   8201744

Please help us to do a better tuning so that we can avoid this error.

We are using Oracle 7.3.3.5 and OpenVMS 7.1 on Alpha Server. We have reserved 108 MB of RAM for SGA. Shared Pool Size is 15MB. 8MB is still free in SGA

INIT.ORA Parameters.

db_block_size = 4096
db_file_multiblock_read_count = 16
db_block_buffers = 20800

shared_pool_size = 15728640
log_checkpoint_interval = 15000
processes = 100
dml_locks = 250
log_buffer = 204800

sequence_cache_entries = 30
sequence_cache_hash_buckets = 23
max_dump_file_size = 10240      # limit trace file size to 5 Meg each
rollback_segments =(r01,r02,r03,r04)

checkpoint_process = TRUE
compatible = 7.3.3
open_cursors = 850
optimizer_mode=RULE
remote_login_passwordfile = EXCLUSIVE
sort_area_size = 262144
global_names = TRUE
# vms_sga_use_gblpagfil = TRUE

_db_block_cache_protect = true                       # memory protect

buffers
event = "10210 trace name context forever, level 2" # data block checking
event = "10211 trace name context forever, level 2" # index block checking
event = "10235 trace name context forever, level 1" # memory heap checking
event = "10049 trace name context forever, level 2" # memory protect cursors
control_files = (ora_control1, ora_control2)

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Sun May 23 1999 - 04:47:30 CDT