nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

insert large DCR

Jens

Hi,

i got an SQL-Execption when i try to put an very large DCR to my database.

Here ist the error message:

...
...
Recurse select: pIndex = 73, value = 1040
BuildTuples:executeQuery returned valid result set
TTableSchemaHelper object for [IN_STICHWORT_WA] found in cache.
BuildTuples:BuildRows returned: true
TTableSchemaHelper object for [IN_STICHWORT_WA] found in cache.
DELETE: DELETE FROM IN_STICHWORT_WA WHERE stichwort_ID IN ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) AND alias_ID IN ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
DoDelete: pIndex = 0, value = 1045
DoDelete: pIndex = 1, value = 1280
DoDelete: pIndex = 2, value = 1040
DoDelete: pIndex = 3, value = 1180
....
DoDelete: pIndex = 203, value = 1145
DoDelete: pIndex = 204, value = 1146

*******************************************************
SQLException occured in TDbSchemaGroupInfoNode
Exception Message: ORA-04031: unable to allocate 24 bytes of shared memory ("shared pool","DELETE FROM IN_STICHWORT_WA ...","sql area","logdef : apaclg")

Vendor Error Code: 4031
SQL state: 61000
*******************************************************

*******STACK TRACE*************
ERROR:Error occured while trying to delete before update
-- Failed
ERROR:WriteTuple failure to consumer[0]
ERROR:Unable to process TeamSite source templatedata/in_marktanalyse/alias/data/alias.dcr, obtained from filelist=/data1/iw-home/local/logs/in/files6638.txt
Filelist walk done
Filelist /data1/iw-home/local/logs/in/files6638.txt deleted
ERROR:deployment [preview] FAILED
Error occured in area outside TDbAgent consumer (explicit rollback initiated)

When I saved the DCR for the first time in an empty table it works (i think the reaseon is, that there is no compare), but when i saved it again without any changes i got the above error.
(There should be made 74 entries in root-table and 131 entries in child-table)

I also doubled the shared memory of the database but nothing happens.

????

Find more posts tagged with

Comments

Migrateduser

can you set real-update="yes" and see if you still see the problem.

PreviewError.jpg

servicegatewayLog.txt

streamstudioLog.txt

Migrateduser

Also check the following:

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

Cause:
More shared memory is needed than was allocated. SGA private memory has been exhausted.

Fragmentation of shared pool memory is a common problem and ORA-04031 is commonly a result of such fragmentation. Application programmers usually get this error while attempting to load a big package or while executing a very large procedure and there is not sufficient contiguous free memory available in the shared pool. This may be due to fragmentation of the shared pool memory or insufficient memory in the shared pool.

Possible remedies:
Use the dbms_shared_pool package to pin large packages.

Attempt to reduce the use of shared memory.

Increase the initialisation parameter ‘SHARED_POOL_SIZE’.

--------------------------------------------------------------------------------

Alternate symptom
An error of the form (Oracle 8.1.5):

ORA-04031: unable to allocate NNNNN bytes of shared memory ("large pool","unknown object","large pool hea","PX msg pool")

Cause:
This error indicates that Oracle is unable to allocate memory from the ‘large’ pool.

To determine the number of free bytes in the ‘large’ pool execute the following SQL:

SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='LARGE POOL' GROUP BY ROLLUP (NAME);

Possible Remedy:
To resolve the problem, consider increasing the value for ‘LARGE_POOL_SIZE’.

Jens

Thank you for reply !!!

I doubled the shared memory again (and again :-) ) and now it works !!!

cute_guy

can you please tell me how to double the shared memory?

I also get the error as below

DAS: ERROR Smiley Frustrated

QLException while selecting rows from table: ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","SELECT SOURCE_SYSTEM,NOTES,A...","sql area","logdef : apaclg")

marcussteiner

talk to your dba. or are you the dba? there is a file in the oracle_home directory path should be $Oracle_home/admin/$Instance_name/pfile/init.ora. Open it and look for SHARED_POOL_SIZE and try to increase the value by 10% or 20%. This should be fine. Then i thinkyou need to restart the instance. Are you on 8i or higher?

cute_guy

Restarting the server solved the problem ... But the error has again cropped up after some time..

I changed the shared_pool_size from 80 MB to 120MB...

error is thrown as

DAS: ERROR Smiley Frustrated

QLException while selecting rows from table: ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","AQ$_QUEUE_TABLES","sql area","logdef : apaclg")

Thanks,
Ganesh

Adam Stoller

Restarting the server solved the problem ... But the error has again cropped up after some time..

I changed the shared_pool_size from 80 MB to 120MB...

error is thrown as

DAS: ERRORQLException while selecting rows from table: ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","AQ$_QUEUE_TABLES","sql area","logdef : apaclg")

Thanks,
Ganesh

Sounds like you have a memory pig process running on your server - and you need to track it down - if you don't know how to do so yourself, there should be someone in your organization (or customer's organization) who knows how to do it.

jkm

We are also getting similar error. Is there any recommended dcr size and shared_pool_memory size interwoven suggests??

I think after changing the size it will work but i read some threads tht it impacts the performance. Is there any optimal combination for this ?

jkm

We increased the shared_memory_size to 1GB but still it is failing.
Any inputs pleasee Smiley Sad

jkm

I turned off the real-update parameter from yes to no...wooo it is working fine after that. Wondering how real-update makes this difference although with real-updates number of queries will be less to the db