查看Oracle AWR后，发现内存有优化的余地，直接修改后，session 挂住，等了很久后中断了会话：http://www.cndba.cn/cndba/dave/article/4159http://www.cndba.cn/cndba/dave/article/4159

cndba@SQL> alter system set sga_target=30G scope=both;
^Calter system set sga_target=30G scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01013: user requested cancel of current operation

sga_target 参数是支持在线调整的，但这里明显是调不动，上MOS搜了一下，解释是内存不足导致的：

ALTER SYSTEM SET SGA_TARGET=’’G Scope=memory Hangs (Doc ID 1946257.1)

http://www.cndba.cn/cndba/dave/article/4159

http://www.cndba.cn/cndba/dave/article/4159

http://www.cndba.cn/cndba/dave/article/4159

The problem lies in the scope=memory option. If there is not enough available memory to increase the sga_target, it causes the statement and the server to hang.

在主机内存不足的情况下，增加sga_target 会导致系统hang住，Oracle 给的建议是先修改spfile，然后重启实例。

cndba@SQL> alter system set sga_target=30G scope=spfile;
System altered.

cndba@SQL> alter system set sga_target=30G scope=memory;
^C alter system set sga_target=30G scope=memory
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01013: user requested cancel of current operation

生产环境，没法直接重启，所以先修改了spfile，等下次窗口，在重启吧。

版权声明：本文为博主原创文章，未经博主允许不得转载。