ORA-04031とは

ORA-04031とはSGA内の共有プールやラージプール等からメモリを割り当てることができなかった場合に発生するエラーです。 ORACLEのほとんどの処理では共有プールからのメモリ割り当てが必要になるため共有プールでORA-04031が発生する状態はデータベースへの接続さえできなくなる可能性がありほぼデータベースは使用不可の状態になります。 なお、ORA-04031は解放可能な領域の解放を試みても領域が確保できなかった場合にエラーが発生するためalter system flush shared_poolを実行しても状況は改善されません。 自然解消する場合もありますが、基本的には即インスタンスを再起動させたほうがサービスのダウンタイムは少なくなります。


対策

まず、エラー時の引数で枯渇が発生した領域を判断することが可能です。(ORACLEのバージョンによって出力内容は異なる) 以下の例ではエラーの第一引数が"shared pool"となっているため共有プールで枯渇が発生したことがわかります。 
*
行1でエラーが発生しました。:
ORA-00604: 再帰SQLレベル3でエラーが発生しました。
ORA-04031: 共有メモリーの216バイトを割当てできません("shared pool","select /*+ rule */ bucket_cn...","SQLA^337fc737","kccdef: qkxrMemAlloc")
ORA-04031: 共有メモリーの528バイトを割当てできません("shared pool","select /*+ rule */ bucket, e...","SQLA^bbcee4f7","qerixs : rixalo")
ORA-06512: 行16
枯渇した領域が共有プールであれば共有プールを広げることで発生を防ぐことができますが、共有プールの仕組みは内部的な仕様が多くあるためサポートサイトのナレッジベースの確認やサポートセンターへの相談等をお勧めします。 ラージプールやストリームプールでのORA-04031であれば領域を動的に拡張(alter systemでパラメータ値を増加させる)可能な設定になっていればデータベースの再起動までせずとも復旧できる場合が多いです。

再現方法

以下のようにSGA上のカーソルを全てdbms_shared_poolプロシージャによりピンし、解放を禁止してしまうことで共有プール枯渇によるORA-04031を再現することができます。 
conn / as sysdba
declare
  i number;
  j char(30);
  sqlstr varchar2(100);
  cursor cur_pin is
   select rawtohex(address) || ',' || to_char(hash_value)
   from sys.v_$sql where kept_versions=0;
  rec_pin varchar2(1000);
begin
  i :=1;
  loop
    loop
      i := i+1;
      exit when i mod 1000 = 0;
      sqlstr := 'select ''' || i || ''' from dual';
      execute immediate sqlstr into j;
    end loop;
    open cur_pin ;
    loop
      fetch cur_pin into rec_pin;
      exit when cur_pin%notfound;
      dbms_shared_pool.keep(rec_pin,'x');
    end loop;
    close cur_pin;
  end loop;
end;
/
★ORACLE案件承ります