当前位置:操作系统 > Unix/Linux >>

ORA-02097、ORA-00837修改memory_target时碰到的一个小问题

ORA-02097、ORA-00837修改memory_target时碰到的一个小问题
 
今天看到一ocp的题有点疑问,就做了个小实验,碰到了ORA-02097、ORA-00837两个错误;
题如下:
Q484. You set the following parameters in the parameter fileand restarted the database:
MEMORY_MAX_TARGET=0
MEMORY_TARGET=500M
PGA_AGGREGATE_TARGET=90M
SGA_TARGET=270M
Which 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

 

我以为应该B正确而不是A;经过实验,答案却是没问题;
 
实验过程
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 8月 3 09:54:47 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter mem

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
shared_memory_address                integer     0

SQL> show parameter pga


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 194M

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 800M
sga_target                           big integer 700M

 

 
出现错误的地方:
 
SQL> alter system set memory_target=900M scope=both;
alter system set memory_target=900M scope=both
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-00837: 指定的值 MEMORY_TARGET 大于 MEMORY_MAX_TARGET

 

 
既然不能对现有的值做调整,那就改用重启后生效的策略
SQL> alter system set memory_target=900M scope=spfile;

 

 
系统已更改。
 
看来,这样是可以的,在重启前建议先创建一个pfile,以免启动不起来时能够手工调整
SQL> create pfile='d:\pfile_t.ora' from spfile;

文件已创建。

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  836976640 bytes
Fixed Size                  1377812 bytes
Variable Size             683674092 bytes
Database Buffers          146800640 bytes
Redo Buffers                5124096 bytes

 

数据库装载完毕。
数据库已经打开。
看来是修改成功了,接下来看看出现了什么情况
 
SQL> show parameter mem

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 900M
memory_target                        big integer 900M
shared_memory_address                integer     0
memory_max_target 已经被自动调整到900M

SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 194M

SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 800M
sga_target                           big integer 700M

 

 
pga_aggregate_target  和 sga_target  保持不变
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,