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

hi,

just a hint:

i had the same problem under digital unix 4.0d with a 7.3.3 database

i checked the kernel parameters and found out that the maximum shared segment size was too small. parameter SHMMAX must be set higher than the SGAsize

  <ad>

kgsudhi_at_my-dejanews.com wrote:

> 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 - 08:10:13 CDT