【DB笔试面试538】在Oracle中， 数据库的参数分为哪几类？
- 2019 年 10 月 10 日
- 筆記
♣
题目部分
在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的区别如下表所示：
|
命令 |
解释 |
|---|---|
|
ALTER SESSION |
修改的参数只限于本次会话，退出会话再进入时修改失效 |
|
ALTER SYSTEM |
修改的参数适用于数据库实例的所有会话，数据库关闭则修改失效。有特权用户和DBA可以执行 |
|
ALTER SYSTEM DEFERRED |
修改是延迟修改，退出会话，下次进入会话时生效。有特权用户和DBA可以执行 |
Oracle参数变更生效范围如下表所示：
|
参数 |
取值 |
结果 |
动态参数 |
静态参数 |
|---|---|---|---|---|
|
SCOPE |
SPFILE |
表示该修改只对服务器参数有效 |
数据库重启时有效，永久有效 |
数据库重启时有效，永久有效，静态参数只适于SPFILE |
|
MEMORY |
表示该修改只对内存有效 |
立即有效，但不产生永久效果，因为没有修改服务器参数 |
不允许使用 |
|
|
BOTH |
表示该修改对上述两种都有效 |
立即有效，永久有效，使用BOTH选项实际上等同于不带参数的ALTER SYSTEM语句 |
不允许使用 |
在RAC环境中，若想修改所有实例，则可以在ALTER SYSTEM的最后加上“SID='*'”或“SID='实例名'”即可，其中，“*”代表所有实例。
下面详细来介绍各种参数。
（三）普通参数
普通参数就是Oracle系统正常使用的一些参数。查询Oracle初始化参数的方式有如下几种：
表 3-13 查询Oracle初始化参数的方式
|
查询命令 |
含义 |
|---|---|
|
SHOW PARAMETERS/SHOW PARAMETER |
SQL*Plus工具提供的查询初始化参数的方法，这个方法查询的初始化参数是当前会话生效的初始化参数。 |
|
SHOW SPPARAMETERS/SHOW SPPARAMETER |
SQL*Plus工具提供的方法，用来查询当前会话生效的SPFILE参数包含的初始化参数。这个命令在Oracle 11g以后SQL*Plus版本中有效。 |
|
CREATE PFILE |
CREATE PFILE命令不像其它方法那样直观，这种方法可以将SPFILE中或当前内存中设置的初始化文件保存到PFILE文件中，然后就可以通过文本编辑工具直观地看到SPFILE中或当前内存中设置了哪些初始化参数。虽然这种方法看上去比较麻烦，但是这种方法列出的参数都是用户设置的参数，所有默认值的参数并不会列出来，因此看到的结果要比其它方法直观得多。在Oracle 11g以后的版本允许执行CREATE PFILE FROM MEMORY操作。 |
|
V$PARAMETER |
V$PARAMETER视图提供了当前会话可见的初始化参数的设置，如果想查询RAC数据库的所有实例的设置，那么可以查询GV$PARAMETER视图。该视图底层来自于X$KSPPCV。 |
|
V$PARAMETER2 |
V$PARAMETER2视图和V$PARAMETER差不多，唯一的区别在于对于包括多值的初始化参数，从这个视图会返回多条记录，每条记录对应一个值。同样的，对于RAC环境可以查询GV$PARAMETER2视图。该视图底层来自于X$KSPPCV2。 |
|
V$SYSTEM_PARAMETER |
V$SYSTEM_PARAMETER视图记录当前实例生效的初始化参数设置。注意这里是实例生效而不是会话生效。同样，GV$SYSTEM_PARAMETER则包含了所有实例生效的初始化参数信息。 |
|
V$SYSTEM_PARAMETER2 |
V$SYSTEM_PARAMETER2视图与V$SYSTEM_PARAMETER视图的关系和V$PARAMETER2视图与V$PARAMETER视图的关系一样，都是对于包含多个值的参数采用了分行处理的方式。 |
|
V$SPPARAMETER |
V$SPPARAMETER记录了来自SPFILE文件中初始化参数。如果参数在SPFILE文件中没有设置，那么字段ISSPECIFIED对应的值为FALSE。同样可以查询GV$SPPARAMETER参数来显示RAC环境所有实例的设置。 |
一般在查询初始化参数的时候都习惯性地使用SHOW PARAMETER，也就是查询V$PARAMETER视图。V$PARAMETER视图反映的是初始化参数在当前会话中生效的值，而V$SYSTEM_PARAMETER反映的才是实例级上的初始化参数。有关视图V$PARAMETER的解释参考下表：
表 3-14 V$PARAMETER视图解释
|
列 |
数据类型 |
说明 |
参数举例 |
|---|---|---|---|
|
NUM |
NUMBER |
参数NUM |
processes、sessions |
|
NAME |
VARCHAR2(80) |
参数名 |
processes、sessions |
|
type |
number |
参数类型：1 – Boolean2 – String3 – Integer4 – Parameter file5 – Reserved6 – Big integer |
processes、timed_statistics |
|
VALUE |
VARCHAR2(4000) |
会话的当前值，若没有用ALTER SESSION修改过参数值，则当前值就是实例级的参数值 |
processes、timed_statistics |
|
DISPLAY_VALUE |
VARCHAR2(4000) |
和VALUE列的值一样，不过该列的值显示更加友好 |
processes、timed_statistics |
|
ISDEFAULT |
VARCHAR2(9) |
参数是否为缺省值 |
processes、timed_statistics |
|
ISSES_MODIFIABLE |
VARCHAR2(5) |
若值为TRUE，则代表参数可用ALTER SEEEION更改；若值为FALSE，则代表参数不能用ALTER SEEEION更改 |
processes、timed_statistics |
|
ISSYS_MODIFIABLE |
VARCHAR2(9) |
若值为IMMEDIATE，则代表参数可用ALTER SYSTEM更改，且立刻生效，该参数属于动态参数；若值为DEFERRED，则代表参数可以用ALTER SYSTEM更改，但是在新连接的会话中生效，该参数属于动态参数；若值为FALSE，则代表参数不能使用ALTER SYSTEM更改，但是若当前参数文件使用的是SPFILE，则可以使用ALTER SYSTEM更改，且下次实例启动生效，该参数属于静态参数 |
recyclebin、instance_name |
|
ISINSTANCE_MODIFIABLE |
VARCHAR2(5) |
若值为TRUE，则代表参数可以在不同的实例上设置不同的值。若值为FALSE，则代表参数在RAC的所有实例上必须设置相同的值。如果ISSYS_MODIFIABLE列为FALSE，则该列也为FALSE |
processes、timed_statistics |
|
ISMODEIFIED |
VARCHAR2(10) |
若值为MODIFIED，则代表值已经被命令ALTER SESSION修改过；若值为SYSTEM_MOD，则代表该参数值已经被命令ALTER SYSTEM修改过；若值为FALSE，则代表自从实例启动后该参数没有被修改过 |
processes、timed_statistics |
|
ISADJUSTED |
VARCHAR2(5) |
指出数据库是否调整输入值。例如参数值应该为素数，但用户输入一个非素数，因此数据库将该值调整为下一个素数） |
processes、timed_statistics |
|
DESCRIPTION |
VARCHAR2(255) |
有关此参数的一个描述性的注释 |
processes、timed_statistics |
|
ISDEPRECATED |
VARCHAR2(5) |
标识该参数是否已废弃 |
lock_name_space、instance_groups |
|
ISBASIC |
VARCHAR2(5) |
标识该参数是否是一个基本参数 |
lock_name_space、timed_statistics |
|
UPDATE_COMMENT |
VARCHAR2(255) |
最近更新过的注释 |
processes、sessions |
|
HASH |
NUMBER |
该参数的HASH值 |
processes、sessions |
（四）非凡参数
下面分别讲解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程序员面试笔试宝典》，作者：李华荣。
