１、SGA_TARGET始終要= SGA_MAX_SIZE 否則 ORA-00823: Specified value ofsga_target greater than sga_max_size AMM啟動時（即memory_target=非0） 1、sga_target《memory_target《memory_max_target 如果 sga_targetmemory_target，則會提示： ORA-00838:

１、SGA_TARGET始終要<=SGA_MAX_SIZE

否則

ORA-00823: Specified value ofsga_target greater than sga_max_size

AMM啟動時（即memory_target=非0）

1、sga_target《memory_target《memory_max_target

如果sga_target>memory_target，則會提示：

ORA-00838:指定的 MEMORY_TARGET 的È太小, 至少應(yīng)為 XXXX M（該È等于sga_target與pga_aggregate_target之和，

因為MEMORY_TARGET》=sga_target與pga_aggregate_target之和）

2、sga_max_size《memory_target ，否則

ORA-00851:SGA_MAX_SIZE 7801404 cannot be set to more than MEMORY_TARGET 41943

AMM關(guān)閉時（即memory_target=0）

1、sga_target

ORA-00849:SGA_TARGET 524288000 cannot be set to more than MEMORY_MAX_TARGET 0.

2、sga_max_size可以大于memory_max_target嗎？

在參數(shù)文件里，即使當(dāng)sga_target《memory_max_target時，如果sga_max_size>memory_max_target，則啟動實例時也不會成功，也會出現(xiàn)提示與sga_target>memory_max_target時相同的提示：ORA-00849:SGA_TARGET 524288000 cannot be set to more than MEMORY_MAX_TARGET 0.

只當(dāng)參數(shù)文件里sga_max_size和memory_max_target都為0時，啟動實例后，顯示的sga_max_size可以大于memory_max_target。

總之，

AMM啟動時（即memory_target=非0），sga_max_size《memory_target：

sga_target《sga_max_size《memory_target

AMM關(guān)閉時（即memory_target=0），由于memory_target=0相當(dāng)于說memory_target這個參數(shù)不起作用此時，所以sga_max_size《memory_max_target：
sga_target《sga_max_size《memory_max_target

原本以為

AMM啟動時（即memory_target=非0），sga_target只要《memory_target，以及<=SGA_MAX_SIZE就行，sga_max_size和memory_target間可以無相關(guān)關(guān)系；AMM關(guān)閉時（即memory_target=0），sga_target只要《memory_max_target以及<=SGA_MAX_SIZE就行，sga_max_size和memory_max_target間可以無相關(guān)關(guān)系。

事實是，在sga_max_size和memory_max_target（或是memory_target）間是有相關(guān)關(guān)系的。

估計因為oracle公司設(shè)計程序時考慮到如下效率問題吧：

如果在sga_max_size和memory_max_target（或是memory_target）間無相關(guān)關(guān)系時，

每次修改一次sga_target的È，程序都要進行兩次判斷sga_target修改后的È是否符合條件：

一是sga_target是否《memory_max_target（或是memory_target），二是sga_target是否<=SGA_MAX_SIZE

而如果在sga_max_size和memory_max_target（或是memory_target）間有相關(guān)關(guān)系，即sga_max_size <=memory_max_target（或是memory_target）時，

每次修改一次sga_target的È，程序只要進行一次判斷sga_target修改后的È是否符合條件即可：

sga_target是否<=SGA_MAX_SIZE.

注釋小結(jié)：

1、AMM啟動時（即memory_target=非0），才會出現(xiàn)

ORA-00851:SGA_MAX_SIZE 7801404 cannot be set to more than MEMORY_TARGET 41943

AMM啟動時（即memory_target=非0）且sga_max_size>memory_target時，才會出現(xiàn)

ORA-00851。

當(dāng)memory_target=0時，由于memory_target=0而使memory_target參數(shù)不起作用，所以以memory_max_target為標(biāo)準(zhǔn)線，sga_max_size和sga_target圍繞memory_max_target，即sga_max_size和sga_target與memory_max_target間有關(guān)系存在。

2、

啟動實例時

SGA_TARGET要小于等于MEMORY_MAX_TARGET ORA-00849（MEMORY_TARGET=0AMM關(guān)閉下，當(dāng)SGA_MAX_SIZE大于等于MEMORY_MAX_TARGET時就會發(fā)生ORA-00849，即使SGA_TARGET小于等于MEMORY_MAX_TARGET也會發(fā)生ORA-00849）

SGA_MAX_SIZE 要小于等于 MEMORY_TARGET ORA-00851

（當(dāng)SGA_MAX_SIZE =非0，MEMORY_TARGET=0時例外，因為MEMORY_TARGET=0AMM關(guān)閉即MEMORY_TARGET參數(shù)不起作用，所以此時SGA_MAX_SIZE 與MEMORY_TARGET無關(guān)系）

實例運行時

SGA_MAX_SIZE 可以大于（修改后的）MEMORY_TARGET

無論啟動實例時，還是實例運行時

MEMORY_TARGET要小于等于MEMORY_MAX_TARGET

（啟動實例前，MEMORY_TARGET=非0，MEMORY_MAX_TARGET=0例外）

SGA_TARGET要小于等于SGA_MAX_SIZE

MEMORY_TARGET》=sga_target與pga_aggregate_target之和

因為無論啟動實例時，還是實例運行時

MEMORY_TARGET》=sga_target與pga_aggregate_target之和

所以SGA_TARGET始終要小于等于MEMORY_TARGET

注釋：

如果兩個參數(shù)都是靜態(tài)或是動態(tài)參數(shù)，那討論她兩關(guān)系時就不用區(qū)分實例啟動前修改它們È和實例啟動后（即實例運行時）修改它們È兩個階段。

如果兩個參數(shù)，一個是靜態(tài)，一個是動態(tài)參數(shù)，那討論她兩關(guān)系時就要區(qū)分實例啟動前修改它們È和實例啟動后（即實例運行時）修改它們È兩個階段。同一個層級上的兩個參數(shù)只討論實例啟動前修改它們È這個階段。例如，MEMORY_TARGET和MEMORY_MAX_TARGET都是AMM程序里的參數(shù)：

MEMORY_TARGET要小于等于MEMORY_MAX_TARGET，

當(dāng)MEMORY_TARGET=非0，MEMORY_MAX_TARGET=0時，啟動實例后MEMORY_MAX_TARGET=MEMORY_TARGETÈ。

上下層級的兩個參數(shù)區(qū)分實例啟動前修改它們È和實例啟動后（即實例運行時）修改它們È兩個階段來討論。例如，SGA_MAX_SIZE和 MEMORY_TARGET

是AMM程序里上下層級上的參數(shù)：

啟動實例時

SGA_MAX_SIZE 要小于等于 MEMORY_TARGET ORA-00851

實例運行時

SGA_MAX_SIZE 可以大于（修改后的）MEMORY_TARGET

因為

memory_max_target是靜態(tài)參數(shù)，其實例運行期間修改的È在實例運行期間不起作用，該參數(shù)只在實例啟動時起作用。

附加注釋：

實例以workarea_size_policy=manual啟動實例時可以在參數(shù)文件里pga_aggregate_target 可以設(shè)置為0，實例啟動后顯示的pga_aggregate_target 的È為0。

從這個結(jié)論，說明有些動態(tài)參數(shù)也要分實例啟動前后討論。

下面是實驗：

memory_target=0

提示ORA-00849: SGA_TARGET 1048576000(是1000M,不是500M) cannot be setto more than MEMORY_MAX_TARGET 943718400.

memory_max_target < sga_max_size：

memory_max_target >sga_target時

參數(shù):

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=500M

SQL> startuppfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00843: Parameter not takingMEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 1048576000(是1000M,不是500M) cannot be setto more than MEMORY_MAX_TARGET 943718400.

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

memory_max_target

sga_max_size> sga_target

參數(shù):

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=950M

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00843: Parameter not takingMEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 1048576000(是1000M,不是950M) cannot be setto more than MEMORY_MAX_TARGET 94

3718400.

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

sga_max_size< sga_target

參數(shù):

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=1200M

SQL> startuppfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00843: Parameter not takingMEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 1048576000(是1000M,不是1200M) cannotbe set to more than MEMORY_MAX_TARGET 943718400.

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

說明

同時出現(xiàn)sga_max_size< sga_target和memory_max_target

memory_max_target > sga_max_size：

memory_max_target

參數(shù):

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=800M

*.sga_target=1000M

SQL> startuppfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00823:Specified value of sga_target greater than sga_max_size

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

總之，memory_參數(shù)和sga_參數(shù)間的關(guān)系出錯（即memory_max_target memory_target

；memory_ target memory_target）都是優(yōu)先與sga_max_size

memory_max_target >sga_target時

sga_max_size> sga_target

參數(shù):

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=800M

*.sga_target=700M

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

啟動實例成功

sga_max_size< sga_target

參數(shù):

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=700M

*.sga_target=800M

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00823: Specified value of sga_targetgreater than sga_max_size

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

說明

同時出現(xiàn)sga_max_size< sga_target和memory_max_target

附加：

當(dāng)memory_target=0時，由于memory_target=0而使memory_target參數(shù)不起作用，所以以memory_max_target為標(biāo)準(zhǔn)線，sga_max_size和sga_target圍繞memory_max_target，即sga_max_size和sga_target與memory_max_target間有關(guān)系存在。

memory_target=非0

提示ORA-00851: SGA_MAX_SIZE1048576000 cannot be set to more than MEMORY_TARGET 629145600.

以memory_max_target為標(biāo)準(zhǔn)線，sga_max_size和sga_target圍繞此

memory_max_target < sga_max_size且memory_target>sga_target時

參數(shù):

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=500M

SQL> shutdown immediate

數(shù)據(jù)庫已經(jīng)關(guān)閉。

已經(jīng)卸載數(shù)據(jù)庫。

ORACLE 例程已經(jīng)關(guān)閉。

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00844: Parameter not takingMEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 1048576000 cannot be set to more thanMEMORY_TARGET 6291

45600.

注釋：

memory_max_target < sga_max_size時，還是提示memory_ target和 sga_max_size間的關(guān)系，說明memory_target=非0時是memory_target和 sga_max_size間有關(guān)系。

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

以memory_ target為標(biāo)準(zhǔn)線，sga_max_size和sga_target圍繞此。

memory_target < sga_max_size且memory_target >sga_target時

pga_aggregate_target與sga_target之和< memory_target

參數(shù):

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=500M

SQL> shutdown immediate

數(shù)據(jù)庫已經(jīng)關(guān)閉。

已經(jīng)卸載數(shù)據(jù)庫。

ORACLE 例程已經(jīng)關(guān)閉。

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00844: Parameter not takingMEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 1048576000 cannot be set to more thanMEMORY_TARGET 6291

45600.

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

pga_aggregate_target與sga_target之和>memory_target

參數(shù):

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=200M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=500M

SQL> shutdown immediate

數(shù)據(jù)庫已經(jīng)關(guān)閉。

已經(jīng)卸載數(shù)據(jù)庫。

ORACLE 例程已經(jīng)關(guān)閉。

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00844: Parameter not takingMEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 1048576000 cannot be set to more thanMEMORY_TARGET 6291

45600.

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

參數(shù):

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=200M

*.workarea_size_policy='AUTO'

*.sga_max_size=600M

*.sga_target=500M

SQL> startuppfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00838: Specifiedvalue of MEMORY_TARGET is too small, needs to be at least 7

00M

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

說明

同時出現(xiàn)pga_aggregate_target與sga_target之和>memory_target和memory_ target < sga_max_size時，優(yōu)先報錯后者的相關(guān)錯誤消息，即ORA-00851:SGA_MAX_SIZE 1048576000 cannot be set to more than MEMORY_TARGET 6291

45600.

memory_ target < sga_max_size且memory_target

sga_max_size> sga_target

參數(shù):

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=800M

*.sga_target=700M

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00844: Parameter not takingMEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 838860800 cannot beset to more than MEMORY_TARGET 62914

5600.

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

sga_max_size< sga_target

參數(shù):

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=700M

*.sga_target=800M

SQL> startuppfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00844: Parameter not takingMEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 734003200 cannot beset to more than MEMORY_TARGET 62914

5600.

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

只有當(dāng)memory_ target 〉 sga_max_size且memory_target 〉sga_target時，sga_max_size< sga_target才會抱錯sga_max_size< sga_target的相關(guān)提示ora-00823。

memory_ target 〉 sga_max_size且memory_target

參數(shù):

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=500M

*.sga_target=700M

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00838: Specified value of MEMORY_TARGETis too small, needs to be at least 800M

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

memory_ target 〉 sga_max_size且

memory_target

同時memory_target

MEMORY_TARGET

sga_max_size

參數(shù):

*.memory_max_target=900M

*.memory_target=700M

*.pga_aggregate_target=0

*.workarea_size_policy='MANUAL'

*.sga_max_size=600M

*.sga_target=800M

SQL> startuppfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to beat least 8

12M(粒度)

ORA-01078: 處理系統(tǒng)參數(shù)失敗

SQL>

當(dāng)

同時出現(xiàn)MEMORY_TARGET< sga_target

sga_max_size

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com