asked on

ORA-00604: error occurred at recursive SQL level 2

When my oracle run about several days, it wll show the error message below:

ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4200 bytes of shared memory
("shared pool","TRIGGER$","sga heap","state objects")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4200 bytes of shared memory
("shared pool","unknown object","sga heap","state objects")

What can I do about this?

ASKER CERTIFIED SOLUTION

baonguyen1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vinay_dba

Your shared pool is fragmented.
Try to flush SGA.
You may have to bounce database.
Increase SHARED_POOL_SIZE parameter if it is low.

anand_2000v

the errors can also be due to

<Bug:1397603>
ORA-4031 / SGA memory leak of PERMANENT memory occurs for buffer handles.
work around : use the undocumented parameter _db_handles_cached = 0 . This bug has been resolved in versions above 8.1.7.2

<Bug:1640583>
ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access
This bug has been resolved in versions above 8.1.7.1

< Bug:1318267 >
INSERT AS SELECT statements may not be shared when they should be if TIMED_STATISTICS is set to true . It can lead to ORA-4031
Workaround : set _SQLEXEC_PROGRESSION_COST=0
Resolved in 8.1.7.1

< Bug:1193003 >
Cursors may not be shared in 8.1 when they should be
Resolved after version 8.1.6.2

<Bug:2104071>
ORA-4031/excessive "miscellaneous"
shared pool usage possible. (many PINS) .This is known to affect the XML parser.
resolved after 8.1.7.4