【DB笔试面试538】在Oracle中， 数据库的参数分为哪几类？

♣

题目部分

在Oracle中， 数据库的参数分为哪几类？

♣

答案部分

Oracle数据库根据SPFILE或PFILE中设置的参数来启动数据库。Oracle中的参数，根据系统使用情况可以简单分为两大类：

l 普通参数：Oracle系统正常使用的一些参数。

l 非凡参数：包括4种，过时参数、强调参数、隐含参数及推导参数。

图 3-11 Oracle参数分类

（一）参数类型介绍

初始化参数有两种类型。

静态参数（Static parameters）：影响实例或整个数据库，只能通过更改init.ora或SPFILE的内容来修改。静态参数要求关闭数据库后再重新启动数据库才能生效。无法对当前实例更改静态参数。

动态参数（Dynamix parameters）：可以在数据库联机时更改。有两种类型的动态参数：

会话级别参数仅影响一个用户会话。这类参数的示例有国家语言支持(NLS)参数，这些参数可用于指定排序、日期参数等的国家语言设置。这些参数可以在某个给定会话中使用，并且会在该会话结束时失效。

系统级别参数影响整个数据库和所有会话。这类参数的示例有修改SGA_TARGET值和设置归档日志目标位置。这些参数在指定的SCOPE内保持有效。要使这些参数设置永久有效，必须通过指定SCOPE=BOTH选项或手动编辑PFILE，将这些参数添加到SPFILE。

可以使用ALTER SESSION和ALTER SYSTEM命令更改动态参数。使用ALTER SYSTEM语句的SET子句可以设置或更改初始化参数值。可选的SCOPE子句用于指定更改的作用域，如下所示：

l SCOPE=SPFILE：此更改仅在服务器参数文件中应用。不会对当前实例进行更改。对于动态和静态参数，更改在下一次启动后生效，并且永久保持有效。对于静态参数来说，只允许指定该SCOPE值。

l SCOPE=MEMORY：此更改仅应用到内存中。会对当前实例进行更改，且更改立即生效。对于动态参数，更改立即生效，但不会永久保持，因为服务器参数文件不会进行更新。对于静态参数，不允许指定该值。

l SCOPE=BOTH：此更改会应用到服务器参数文件和内存中。会对当前实例进行更改，且更改立即生效。对于动态参数，更改永久保持有效，因为服务器参数文件会进行更新。对于静态参数，不允许指定该值。

如果实例不是使用服务器参数文件(SPFILE)启动的，则指定SCOPE=SPFILE或SCOPE=BOTH是错误的。如果实例是使用服务器参数文件启动的，则默认值为SCOPE=BOTH；如果实例是使用文本初始化参数文件启动的，则默认值为MEMORY。

Oracle的推导参数（Derived Parameters）也是初始化参数的一种。推导参数值通常来自于其它参数的运算，依赖其它参数计算得出，例如SESSIONS、DML_LOCKS等参数都属于推导参数。该类参数在官方文档中的“Default value”中由关键字Derived标识，例如，DML_LOCKS参数的默认值为：Derived: 4 * TRANSACTIONS，说明该参数为推导参数，它的值默认为参数TRANSACTIONS值的4倍。

如何判断一个初始化参数的值是否是默认参数值？Oracle在视图V$SYSTEM_PARAMETER或V$PARAMETER中提供了一个列ISDEFAULT，表示当前设置的值是否是数据库的默认值。

如何判断一个初始化参数的值是否是延迟生效?是否是动态参数？动态参数指的是可以使用ALTER SESSION或ALTER SYSTEM在数据库运行时进行修改并能立即生效的参数。静态参数指的是只能通过修改参数文件且数据库必须要重启才能生效的参数。Oracle在视图V$PARAMETER中提供了一个列ISSYS_MODIFIABLE，若值为IMMEDIATE，代表参数可用ALTER SYSTEM更改，且立刻生效，该参数属于动态参数；若值为DEFERRED，代表参数可以用ALTER SYSTEM更改，但是在新连接的会话中生效，该参数属于动态参数；若值为FALSE，代表参数不能使用ALTER SYSTEM更改，但是若当前参数文件使用的是SPFILE，则可以使用ALTER SYSTEM更改，且下次实例启动生效，该参数属于静态参数。

静态参数示例如下所示：

SYS@lhrdb> ALTER SYSTEM SET PROCESSES=300;
alter system set processes=300
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

动态参数示例如下所示：

SYS@lhrdb>  ALTER SYSTEM SET UNDO_RETENTION=10800 ;

System altered.

延迟动态参数（DEFERRED）中，DEFERRED指定系统修改是否只对以后的会话生效（对当前建立的会话无效，包括执行此修改的会话）。默认情况下，ALTER SYSTEM命令会立即生效，但是有些参数不能“立即”修改，只能为新建立的会话修改这些参数。

SYS@lhrdb> ALTER SYSTEM SET SORT_AREA_SIZE = 65536;
alter system set sort_area_size = 65536
                                      *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
SYS@lhrdb> ALTER SYSTEM SET SORT_AREA_SIZE = 65536 DEFERRED;
System altered.

（二）参数的设置方法

初始化参数的设置方法有很多种：

l 通过“ALTER SYSTEM/SESSION SET 参数名=参数值 SCOPE = MEMORY;”的方式仅在内存里修改。

l 通过“ALTER SYSTEM SET 参数名=参数值 SCOPE = SPFILE;”的方式只修改SPFILE里的值。

l 通过“ALTER SYSTEM SET 参数名=参数值 DEFERRED SCOPE = SPFILE;”的方式设置延迟生效，也就是说这个修改只对以后连接到数据库的会话生效，而对当前会话以及其它已经连接到Oracle的会话不会生效。

l 通过“ALTER SYSTEM/SESSION SET 参数名=参数值 SCOPE = BOTH;”或省略BOTH这个关键词可以同时修改SPFILE和MEMORY中的值。

ALTER SESSION和ALTER SYSTEM的区别如下表所示：

Oracle参数变更生效范围如下表所示：

在RAC环境中，若想修改所有实例，则可以在ALTER SYSTEM的最后加上“SID='*'”或“SID='实例名'”即可，其中，“*”代表所有实例。

下面详细来介绍各种参数。

（三）普通参数

普通参数就是Oracle系统正常使用的一些参数。查询Oracle初始化参数的方式有如下几种：

表 3-13 查询Oracle初始化参数的方式

一般在查询初始化参数的时候都习惯性地使用SHOW PARAMETER，也就是查询V$PARAMETER视图。V$PARAMETER视图反映的是初始化参数在当前会话中生效的值，而V$SYSTEM_PARAMETER反映的才是实例级上的初始化参数。有关视图V$PARAMETER的解释参考下表：

表 3-14 V$PARAMETER视图解释

（四）非凡参数

下面分别讲解3种非凡参数。

过时参数（Obsolete Parameters），顾名思义就是在Oracle以前的版本中存在，但在新版本中已经淘汰了的参数，已经不再使用的参数。在视图V$OBSOLETE_PARAMETER中，包含这些参数的名称和一个列ISSPECIFIED，该列用来指出这个参数是否在参数文件中已实际设置。下面的SQL脚本列出了当前系统中所有的过时参数名称以及它们是否在当前系统中设定。

 SELECT   NAME, ISSPECIFIED FROM V$OBSOLETE_PARAMETER;

强调参数（Underscored Parameters），是指那些在新版本中保留了下来，但是除非非常需要否则不希望用户使用的那些参数。强调参数可以通过系统视图X$KSPPO来查看，该视图中包含一个名为KSPPOFLAG的字段。该字段用来指明该参数在当前版本中是被丢弃还是被强调。若该值为1，则表示该参数已被丢弃，若该值为2，则表示该参数为强调参数。

SYS@lhrdb> SELECT KSPPONM, DECODE(KSPPOFLG, 1, 'Obsolete', 2, 'Underscored')
  2    FROM X$KSPPO T
  3     WHERE T.KSPPONM IN ('hash_join_enabled','job_queue_interval')
  4   ORDER BY KSPPONM;
KSPPONM                                                          DECODE(KSPP
---------------------------------------------------------------- -----------
hash_join_enabled                                                Underscored
job_queue_interval                                               Obsolete

可以看到HASH_JOIN_ENABLED这个参数为强调参数，在隐含参数中表现为“_HASH_JOIN_ENABLED”，而JOB_QUEUE_INTERVAL已变为了过时参数。

Oracle系统中还有一类参数称之为隐含参数（Hidden Parameters），在系统中使用，但Oracle官方没有公布的参数，这些参数可能是那些还没有成熟或者是系统开发中使用的参数。这些参数在所有Oracle官方提供的文档中都没有介绍，它们的命名有一个共同特征就是都以“_”作为参数的首字符。下面的查询可以得到当前系统中的所有隐藏参数，需要以SYS用户登陆，查看两个视图：X$KSPPI和X$KSPPCV。下面作者给出具体的SQL语句。

举个例子，如果需要查询隐含参数“_LM_DD_INTERVAL”的值，那么执行上面的代码后输入“_LM_DD_INTERVAL”就可以看到该隐含参数的值了，如下所示：

SYS@lhrdb> SET PAGESIZE 9999
SYS@lhrdb> SET LINE 9999
SYS@lhrdb> COL NAME FORMAT A40
SYS@lhrdb> COL KSPPDESC FORMAT A50
SYS@lhrdb> COL KSPPSTVL FORMAT A20
SYS@lhrdb> SELECT A.INDX,
  2         A.KSPPINM NAME,
  3         A.KSPPDESC,
  4         B.KSPPSTVL 
  5  FROM   X$KSPPI  A,
  6         X$KSPPCV B
  7  WHERE  A.INDX = B.INDX
  8  AND A.KSPPINM LIKE '/_%' ESCAPE '/'
  9  AND LOWER(A.KSPPINM) LIKE  LOWER('%&PARAMETER%');
Enter value for parameter: _lm_dd_interval
old   9: AND LOWER(A.KSPPINM) LIKE  LOWER('%&PARAMETER%')
new   9: AND LOWER(A.KSPPINM) LIKE  LOWER('%_lm_dd_interval%')
      INDX NAME                                     KSPPDESC                                           KSPPSTVL
---------- ---------------------------------------- -------------------------------------------------- --------------------
       578 _lm_dd_interval                          dd time interval in seconds                        10

可以看到该隐含参数的值为10。

对于隐含参数而言，修改隐含参数的值的时候需要将隐含参数用双引号括起来。若要清除SPFILE中的隐含参数，可以使用RESET命令，如下所示：

SYS@lhrdb> ALTER SYSTEM SET _LM_DD_INTERVAL=20 SCOPE=SPFILE;
alter system set _lm_dd_interval=20 scope=spfile
                 *
ERROR at line 1:
ORA-00911: invalid character
SYS@lhrdb> ALTER SYSTEM SET "_LM_DD_INTERVAL"=20 SCOPE=SPFILE;
System altered.
SYS@lhrdb> ALTER SYSTEM RESET "_LM_DD_INTERVAL"  SCOPE=SPFILE SID='*';
System altered.

普通用户是不具备查询隐含参数的权限的，可以通过创建视图和同义词的方式来解决这个问题，如下所示：

CREATE OR REPLACE VIEW VW_YH_PARAMETER_LHR AS
SELECT A.INDX, A.KSPPINM NAME, A.KSPPDESC, B.KSPPSTVL
  FROM X$KSPPI A, X$KSPPCV B
 WHERE A.INDX = B.INDX
   AND A.KSPPINM LIKE '/_%' ESCAPE '/' --TRANSLATE (ksppinm, '_', '#') LIKE '#%'
   ;
GRANT SELECT  ON  VW_YH_PARAMETER_LHR TO PUBLIC;
CREATE PUBLIC SYNONYM VW_YH_PARAMETER_LHR FOR SYS.VW_YH_PARAMETER_LHR;

& 说明：

有关Oracle参数的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2127338/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。