ORA:04031 ( Out Of Memory)

Hi , this is the very frequent issue  that comes in database. Here I am sharing some of option to analyse the root cause.

* First of all we need to check trace file and alert log, may be trace file can give you root cause.

* This error also come due to swap memory issue, so we have to consider that on also.

* Issue is due to shared pool fragmentation, which can re-occur as the load increases and may        in a day or two days.

* The primary cause for ORA-04031 is also Hard Parse. So for this we have to genrate AWR        report during the problem Time and should check the load profile section for Hard-                    Parses/Section.

* This should be secound option, we can query v$memory_resize_ops view to get details of            memory consumption.

   (check component,initial_size, target_size,start_time,end_time...) memory resizing occur if          there is a memory pressure.

SQL> desc v$memory_resize_ops;


 Name                                              Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMPONENT                                          VARCHAR2(64)
 OPER_TYPE                                             VARCHAR2(13)
 OPER_MODE                                           VARCHAR2(9)
 PARAMETER                                           VARCHAR2(80)
 INITIAL_SIZE                                          NUMBER
 TARGET_SIZE                                         NUMBER
 FINAL_SIZE                                             NUMBER
 STATUS                                                      VARCHAR2(9)
 START_TIME                                            DATE

 END_TIME                                                DATE