今天碰到 oracle 報錯 ORA-04031,

1
2
ORA-04031: unable to allocate 400 bytes of shared memory 
("shared pool","select /*+ rule */ bucket_cn...","SQLA^337fc737","opixpop:kctdef") [getLotID() INSERT LOT]

查了一下, 發現是
共用記憶體太小, 分配給共用池的記憶體不足以滿足使用者請求
或者存在一定碎片, 沒有有效的利用保留區, 造成無法分配合適的共用區
一個Oracle的系統全域區域(SGA)包含幾個記憶體區域
(包括緩衝快取記憶體、共用池、JAVA 池、大型池和重做日誌緩衝)

1
SGA=db_cache+shared_pool+java_pool+large_pool

相關的查看指令如下:

1
2
3
4
sql> show sga; //查看SGA的具體大小資訊
sql> show parameter sga_max_size //查看SGA最大值
sql> show parameter shared_pool //查看共用記憶體
sql> show parameter db_cache //查看資料緩存

相關的修改參數設定指令如下:

1
2
3
sql> alter system set sga_max_size = 640M scope=spfile; //修改SGA最大值
sql> alter system set shared_pool_size = 128M scope=spfile; //修改共用記憶體
sql> alter system set db_cache_size = 256M scope=spfile; //修改資料緩存

再然後, 試著修改 sga_max_size 時出錯,

1
ORA-32001: write to SPFILE requested but no SPFILE is in use

媽呀~~~原來是這個 instance 連 spfile 都沒建,
趕緊的從 pfile 建一個 spfile.

1
sql> create spfile from pfile;

重啟一下, 讓它用 spfile 啟動

1
shutdown immediate

1
startup

接著就可以執行上面提到的修改 sga_max_size 的指令了.

備註：
pfile 是 init{instance}.ora 的檔案(純文字)
spfile 是 spfile{instance}.ora 的檔案(二進制)

兩者可以切換互相備援

1
2
create spfile from pfile;
create pfile from spfile;

所以如果改了上面的 sga_max_size 然後出現了

1
2
SQL> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 4848M

類似這樣的錯誤, 表示你的 memory_target 設置的數字是不合理的(通常是太小)
最簡單的方式是重新產生 pfile,
因為 pfile 可以編輯, 改完正確參數數值後,
再從 pfile 產生 spfile,
還是要產生 spfile,
因為 spfile 的優先權是比較高的,
否則單純下 startup 指令,
會用設定數值還是不合理的 spfile 起,
一樣會是報錯

然後 memory_target 的數值如果不知道怎麼算,
也可以簡單的設置為 0
讓 oracle 10g 以上的版本系統自動計算.