Oracle DB - 如果不小心設了sga_max_size，如何完整啟動 Automatic Memory Management

1.啟動AMM

ALTER SYSTEM SET memory_target='805306368' SCOPE=BOTH;
ALTER SYSTEM SET sga_target='0' SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target='0' SCOPE=BOTH;

2. 取消sga_max_size parameter(使用AMM不需要此參數)

CREATE PFILE = '$ORACLE_HOME/dbs/my_pfile.ora' FROM SPFILE;

show parameter spfile
--$ORACLE_HOME/dbs/spfileSID.ora

vi $ORACLE_HOME/dbs/my_pfile.ora
找到*.sga_max_size這個值，刪除之

CREATE SPFILE = '$ORACLE_HOME/dbs/my_spfile.ora' FROM PFILE = '$ORACLE_HOME/dbs/my_pfile.ora';

shutdown immediate

cd $ORACLE_HOME/dbs

mv spfileSID.ora spfileSID.ora.bck

mv my_spfile.ora spfileSID.ora

startup