杨廷琨（yangtingkun）
云和恩墨 CTO
高级咨询顾问，Oracle ACE 总监，ITPUB Oracle 数据库管理版版主
多种查询初始化参数方法
查询初始化参数的方法很多，比如 SHOW PARAMETER，或查询 V$PARAMETER 等，这里简单总结一下。
首先初始化参数的设置的方法就有很多种：可以通过 ALTER SYSTEM SET SCOPE = MEMORY 的方式仅设置内存的修改，也可以通过 ALTER SYSTEM SET SCOPE = SPFILE 只修改 SPFILE 的值，当然也可以同时修改 SPFILE 和 MEMORY 中的设置。还有一种初始化参数可以设置延迟生效，也就是说这个修改只对以后连接到数据库的会话生效，而对当前会话以及其他已经连接到 Oracle 的会话不会生效。如果再考虑 RAC 环境，数据库中存在多个启动实例的情况，那么查询数据库初始化参数就更加复杂了。
所幸 Oracle 提供的查询初始化参数方法也有很多种：SHOW PARAMETERS、SHOW SPPARAMETERS、CREATE PFILE、V$PARAMETER、V$PARAMETER2、V$SYSTEM_PARAMETER、V$SYSTEM_PARAMETER2、V$SPPARAMETER。
SHOW PARAMETERS 是 SQLPLUS 工具提供的查询初始化参数的方法，这个方法查询的初始化参数是当前会话生效的初始化参数。
SHOW SPPARAMETERS 也是 SQLPLUS 工具提供的方法，用来查询当前会话生效的 SPFILE 参数包含的初始化参数。这个命令在11g 以后 sqlplus 版本中有效。
CREATE PFILE 命令不像其他方法那样直观，这种方法可以将 SPFILE 中或当前内存中设置的初始化文件保存到 PFILE 文件中，然后就可以通过文本编辑工具直观的看到 SPFILE 中或当前内存中设置了哪些初始化参数。虽然这种方法看上去比较麻烦，但是这种方法列出的参数都是用户设置的参数，所有默认值的参数并不会列出来，因此看到的结果要比其他方法直观得多。在11g 以后的版本允许 CREATE PFILE FROM MEMORY。
V$PARAMETER 视图提供了当前会话可见的初始化参数的设置，如果像查询 RAC 数据库的所有实例的设置，可以查询 GV$PARAMETER 视图。
V$PARAMETER2 视图和 V$PARAMETER 差不多，唯一的区别在于对于包括值的初始化参数，从这个视图会返回多条记录，每条记录对应一个值。同样的，对于 RAC 环境可以查询 GV$PARAMETER2 视图。
V$SYSTEM_PARAMETER 视图记录当前实例生效的初始化参数设置。注意这里是实例生效而不是会话生效。同样，GV$SYSTEM_PARAMETER 则包含了所有实例生效的初始化参数信息。
V$SYSTEM_PARAMETER2 视图与 V$SYSTEM_PARAMETER 视图的关系和 V$PARAMETER2 视图与 V$PARAMETER视 图的关系一样，都是对于包含多个值的参数采用了分行处理的方式。
V$SPPARAMETER 记录了来自 SPFILE 文件中初始化参数。如果参数在 SPFILE 文件中没有设置，则字段 ISSPECIFIED 对应的值为 FALSE。同样可以查询 GVSPPARAMETER 参数来显示 RAC 环境所有实例的设置。
V$PARAMETER 和 V$PARAMETER2 的区别
这个区别同样适用于 V$SYSTEM_PARAMETER 和 V$SYSTEM_PARAMETER2：
SQL> SELECT NAME, VALUE FROM V$PARAMETER
2 MINUS
3 SELECT NAME, VALUE FROM V$PARAMETER2;
NAME VALUE
---------------------------------------- --------------------------------------------------
control_files E:\ORACLE\ORADATA\YTK102\CONTROL01.CTL, E:\ORACLE\
ORADATA\YTK102\CONTROL02.CTL, E:\ORACLE\ORADATA\YT
K102\CONTROL03.CTL
SQL> SELECT NAME, VALUE FROM V$PARAMETER2
2 MINUS
3 SELECT NAME, VALUE FROM V$PARAMETER;
NAME VALUE
---------------------------------------- --------------------------------------------------
control_files E:\ORACLE\ORADATA\YTK102\CONTROL01.CTL
control_files E:\ORACLE\ORADATA\YTK102\CONTROL02.CTL
control_files E:\ORACLE\ORADATA\YTK102\CONTROL03.CTL
V$PARAMETER 和 V$SYSTEM_PARAMETER 视图的区别
一般在查询初始化参数的时候都习惯性的使用 SHOW PARAMETER，也就是查询 V$PARAMETER 视图，但是有些时候查询这个视图得到的结果并不准确：
SQL> show parameter query_rewrite_enabled
NAME TYPE VALUE
------------------------------------ ----------- --------------------
query_rewrite_enabled string TRUE
SQL> select name, value
2 from v$parameter
3 where name = 'query_rewrite_enabled';
NAME VALUE
---------------------------------------- ----------------------------
query_rewrite_enabled TRUE
SQL> select name, value
2 from v$system_parameter
3 where name = 'query_rewrite_enabled';
NAME VALUE
---------------------------------------- ----------------------------
query_rewrite_enabled TRUE
这是如果在会话级修改 query_rewrite_enabled 这个初始化参数：
SQL> alter session set query_rewrite_enabled = false;
会话已更改。
SQL> show parameter query_rewrite_enabled
NAME TYPE VALUE
------------------------------------ ----------- -------------------
query_rewrite_enabled string FALSE
SQL> select name, value
2 from v$parameter
3 where name = 'query_rewrite_enabled';
NAME VALUE
---------------------------------------- ---------------------------
query_rewrite_enabled FALSE
SQL> select name, value
2 from v$system_parameter
3 where name = 'query_rewrite_enabled';
NAME VALUE
---------------------------------------- ---------------------------
query_rewrite_enabled TRUE
可以看到，show parameter 和查询 v$parameter 视图的结果都是 FALSE，而刚才做的修改只是会话级，并没有修改系统的初始化参数。在上一篇描述这两个视图的时候就提到了，V$PARAMETER 视图反映的是初始化参数在当前会话中生效的值，而 V$SYSTEM_PARAMETER 反映的才是实例级上的初始化参数。
再来看看延迟参数修改的情况：
SQL> select name, value
2 from v$parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ------------------------------------
recyclebin on
SQL> select name, value
2 from v$system_parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ------------------------------------
recyclebin on
SQL> alter system set recyclebin = off deferred scope = memory;
系统已更改。
SQL> select name, value
2 from v$parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ------------------------------------
recyclebin on
SQL> select name, value
2 from v$system_parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ------------------------------------
recyclebin OFF
结果和前面的恰好反过来，v$parameter 视图中的结果没有发生变化，而 v$system_parameter 视图的结果变成了 OFF。这是因为延迟修改对数据库中当前存在的会话不生效，因此反映当前会话情况的 v$parameter 视图结果不变，而对于系统而言，初始化参数已经改变，而且所有新建会话的参数也会改变，所以 v$system_parameter 视图的结果发生了改变。
SQL> CONN YANGTK/YANGTK@YTK111
已连接。
SQL> select name, value
2 from v$parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ---------------------------
recyclebin OFF
SQL> select name, value
2 from v$system_parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ---------------------------
recyclebin OFF
根据这两个例子，利用 V$PARAMETER 视图获取系统的启动初始化参数是不准确的，应该从 V$SYSTEM_PARAMETER 视图来获取。
RAC 环境下初始化参数的查询
前文已经提到，使用 SHOW PARAMETER 查询，看到的是当前会话可以看到的初始化参数，那么这个参数导致是全局设置还是当前实例设置的，是从这个命令中看不到的。
虽然 Oracle 提供了 GV$ 开头的初始化参数，可以用来查询两个实例上的设置，但是情况并不是这么简单的。
一个简单的例子：
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300
SQL> alter system set open_cursors = 500 scope = both sid = 'test1';
系统已更改。
SQL> disc
从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options 断开
SQL> set instance test2
Oracle Database 11g Release 11.1.0.0.0 - Production
SQL> conn sys as sysdba
输入口令:
已连接。
SQL> alter system set open_cursors = 400 scope = both sid = 'test2';
系统已更改。
SQL> disc
从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options 断开
SQL> set instance local
Oracle Database 11g Release 11.1.0.0.0 - Production
SQL> conn / as sysdba
已连接。
现在来看看不同的查询方法得到的结果：
SQL> select name, value
2 from v$parameter
3 where name = 'open_cursors';
NAME VALUE
------------------------------ --------------------------------------------------
open_cursors 500
SQL> select inst_id, name, value
2 from gv$parameter
3 where name = 'open_cursors';
INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------
1 open_cursors 500
2 open_cursors 400
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 500
SQL> select sid, name, value
2 from v$spparameter
3 where name = 'open_cursors';
SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* open_cursors 300
test1 open_cursors 500
test2 open_cursors 400
SQL> show spparameter open_cursors
SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* open_cursors integer 300
test2 open_cursors integer 400
test1 open_cursors integer 500
似乎除了看不到全局设置外，GV$PARAMETER 参数和 V$SPPARAMETER 没有什么不同，其实不然，如果 alter system set 的时候只修改了 spfile 或只修改了 memory 参数，结果就会不同：
SQL> alter system set open_cursors = 600 scope = memory sid = 'test1';
系统已更改。
SQL> alter system set open_cursors = 700 scope = spfile sid = 'test2';
系统已更改。
SQL> select name, value
2 from v$parameter
3 where name = 'open_cursors';
NAME VALUE
------------------------------ --------------------------------------------------
open_cursors 600
SQL> select inst_id, name, value
2 from gv$parameter
3 where name = 'open_cursors';
INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------
1 open_cursors 600
2 open_cursors 400
SQL> select sid, name, value
2 from v$spparameter
3 where name = 'open_cursors';
SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* open_cursors 300
test1 open_cursors 500
test2 open_cursors 700
从上面的对比就可以看出，通过 GV$ 视图访问的结果和 SPFILE 中包含的信息其实是两回事。
除了上面介绍的几种视图之外，CREATE PFILE 其实也是一个不错的选择，在10g 以前只能 CREATE PFILE FROM SPFILE，得到的结果类似于对 VSPPARAMETER 视图的查询，而11g增加了 CREATE PFILE FROM MEMORY 选项，这个得到的结果类似于从 GV$SYSTEM_PARAMETER 视图获取的查询。
未完待续。。。
如何加入云和恩墨大讲堂微信群
搜索盖国强（Eygle）微信号：eeygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。
云和恩墨
数据驱动，成就未来。整合业界顶尖的技术与合作伙伴资源，围绕数据及相关领域，提供解决方案和专业服务。
业务架构
电子渠道（网络销售）分析系统、数据治理
IT基础架构
分布式存储解决方案
数据架构
Oracle DB2 MySQL NoSQL
专项服务：架构/安全/容灾/优化/整合/升级/迁移
运维服务：运维服务 代维服务
人才培养：个人认证 企业内训
软件产品：SQL 审核、监控、数据恢复
应用架构
应用软件开发管控：数据建模| SQL审核和优化
恩墨学院
恩墨学院是云和恩墨(北京)信息技术有限公司旗下的培训事业部，创业数年专注于数据库认证、技能培训，以专业的讲师塑造品牌，以专业的训练保证就业，目前已经发展成为国内数据库领域培训领导品牌。
点击阅读原文报名参加本周六北京活动
5
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
