A small problem encountered when ORA-02097 and ORA-00837 modify memory_target

ORA-02097, ORA-00837 modify memory_target encountered a small problem today saw an ocp question a little doubt, did a small experiment, encountered two errors of ORA-02097, ORA-00837; the question is as follows:

Q484. You set the following parameters in the parameter fileand restarted the database:MEMORY_MAX_TARGET=0MEMORY_TARGET=500MPGA_AGGREGATE_TARGET=90MSGA_TARGET=270MWhich two statements are true regarding these parameters afterthe database instance is restarted? (Choose two.)A. The MEMORY_MAX_TARGET parameter is automatically set to500 MB.B. The value of the MEMORY_MAX_TARGET parameter remains zerotill it is changed manually.C. The PGA_AGGREGATE_TARGET and SGA_TARGET parameters areautomatically set to zero.D. The lower bounds of PGA_AGGREGATE_TARGET and SGA_TARGETparameters are set to 90 MB and 270 MB, respectively.Answer: A,D

 

I thought B should be correct, not A; after the experiment, the answer is OK; during the experiment, Microsoft Windows XP [version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. c: \ Documents ents and Settings \ Administrator> sqlplus/as sysdbaSQL * Plus: Release 11.2.0.1.0 Production on Saturday August 3 09:54:47 2013 Copyright (c) 1982,201 0, Oracle. all rights reserved. connect:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> show parameter memNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------hi_shared_memory_address             integer     0memory_max_target                    big integer 0memory_target                        big integer 0shared_memory_address                integer     0SQL> show parameter pgaNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------pga_aggregate_target                 big integer 194MSQL> show parameter sgaNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------lock_sga                             boolean     FALSEpre_page_sga                         boolean     FALSEsga_max_size                         big integer 800Msga_target                           big integer 700M

 

Where an error occurs:

SQL> alter system set memory_target = 900 M scope = both; alter system set memory_target = 900 M scope = both * row 1st error: ORA-02097: Unable to modify parameters, because the specified value is invalid ORA-00837: the specified value MEMORY_TARGET is greater than MEMORY_MAX_TARGET

 

Since the existing values cannot be adjusted, use the policy that takes effect after restart.

SQL> alter system set memory_target=900M scope=spfile;

 

The system has been changed. It seems that this is acceptable. We recommend that you create a pfile before restarting to avoid manual adjustment when the file cannot be started.

SQL> create pfile = 'd: \ pfile_t.ora 'from spfile; the file has been created. SQL> shutdown immediate; the database has been closed. The database has been detached. The ORACLE routine has been disabled. The SQL> startupORACLE routine has been started. Total System Global Area 836976640 bytesFixed Size 1377812 bytesVariable Size 683674092 bytesDatabase Buffers 146800640 bytesRedo Buffers 5124096 bytes

 

The database has been loaded. The database has been opened. It seems that the modification is successful. Next, let's take a look at the situation.

SQL> show parameter memNAME TYPE VALUE exceed ----------- adjust parameter integer 0memory_max_target big integer 900Mmemory_target big integer between integer 0memory_max_target has been automatically adjusted to 900 MSQL> show parameter pgaNAME TYPE VALUE exceed ----------- too big integer 194 MSQL> show parameter sgaNAME type value generation ------------- ------------------------------ lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 800Msga_target big integer 700 M

 

Pga_aggregate_target and sga_target remain unchanged