Oracle参数的查询和修改方法

57次阅读

共计 9210 个字符，预计需要花费 24 分钟才能阅读完成。

这篇文章主要讲解了“Oracle 参数的查询和修改方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“Oracle 参数的查询和修改方法”吧！

测试环境

DB Version: oracle 11.2 RAC

OS: RHEL 6.x

v$parameter, v$parameter2, v$system_parameter, v$system_parameter2, v$spparameter 的区别

Oracle 11g 里主要的查询参数的视图有 v$parameter, v$parameter2, v$system_parameter, v$system_parameter2, v$spparameter, dba_hist_parameter

v$parameter 和 v$parameter2 有什么区别呢？就跟 v$system_parameter 和 v$system_parameter2 的区别是一样的。呵呵，具体为:

v$parameter 里存的是每个 parameter 的 value, 一个 parameter 一条记录。v$parameter2 也存的是每个 parameter 的 value, 不过在 v$parameter2 里是每行的 name 只会存一个 value。以 control_files 为例，这个 parameter 会对应至少两个值，那么在 v$parameter 里只会有一条记录，而在 v$parameter2 里却会有 2 条记录。其实真想不明白 oracle 为啥要设计出一个这样的视图来，使用场景在哪？欢迎有知道的童鞋告诉我下

v$parameter 和 v$system_parameter 的区别：v$parameter 是存储当前 session 的 parameter/value, 而 v$system_parameter 存储的是当前 instance 级别的 parameter/value; 也就是说一个新建立的 session，v$parameter 和 v$system_parameter 里面的 parameter/value 是一致的; 因为 session level parameter 是从 instance level parameter 继承来的; 然后通过 alter session 可以修改 v$parameter 的值，但是并不会修改 v$system_parameter data

我尝试从 session1 执行一个 alter session, 然后再 session1 看到 v$parameter 值是变化了，打开 session2, 看到的 v$parameter 的值还是老的值，这我就困惑了，不是说 v$parameter 能看到 alter session 之后的值吗？为啥 session1 和 session2 看到的结果不一样呢? 这就是因为 v$parameter 显示的是当前 session 的 parameter name/value 呀.

v$spparameter 和 v$system_parameter: 这两个 view 的区别在于: v$spparameter 用于存储 spfile 里的 parameter name/value, 而 v$system_parameter 用于存储 instance level 当前的 parameter name/value; 体现在哪呢？

例子

SQL select name, value from v$system_parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$spparameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

———— 执行 alter session 之后三张表的查询结果

Session 1:

SQL select userenv(sid) from dual;

USERENV(SID)

————–

569

SQL alter session set cursor_sharing= FORCE

Session altered.

SQL select name, value from v$parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing FORCE

SQL select name, value from v$spparameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$system_parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

Session 2:

SQL select userenv(sid) from dual;

USERENV(SID)

————–

853

SQL select name, value from v$parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$spparameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$system_parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

从这里可以看到通过 alter session 修改之后，当前 session1 里查 v$parameter, cursor_sharing 已经改成了 FORCE, 但是另外一个 session 的 v$parameter 里的值并没有改变; 通过 v$system_parameter 和 v$spparameter 里的值也没有改变；

例子 2:

仅仅修改 v$spparameter

通过 alter system set … scope=spfile，这样修改的参数就只会进入 spfile 里，所以只有 v$spparameter 才能看到;

修改前

SQL select name, value from v$parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$parameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors 500

SQL select name, value from v$system_parameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors 500

SQL select name, value from v$spparameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors 500

修改后

SQL alter system set open_cursors=3000
scope=spfile sid= *

System altered.

SQL select name, value from v$system_parameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors
500

SQL select name, value from v$spparameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors
3000

SQL select name, value from v$parameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors
500

例子 3

只修改 v$system_parameter

修改前

SQL select name, value from v$spparameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count

注意这里显示的是 NULL，表示这个 parameter value 在 spfile 里没有设置，但是 v$system_parameter 里又有值，这是因为系统会根据一定的规则自己计算出一个值来

SQL select name, value from v$system_parameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count 128

SQL select name, value from v$parameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count 128

修改后

SQL alter system set db_file_multiblock_read_count=256
scope=memory sid= *

System altered.

SQL select name, value from v$spparameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count

SQL select name, value from v$system_parameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count 256

SQL select name, value from v$parameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count 256

v$spparameter 里的值仍然为空，v$system_parameter 的值改为了 256, 并且并且 v$parameter 里的值也是 256. 这说明什么问题？说明如果一个参数没有执行 alter session, 那么这个参数的值在 v$parameter 和 v$system_parameter 里的值是一样的;

当然也可以通过 alter system set … scope=BOTH sid= * 让改动在 v$spparameter 和 v$system_parameter 里都能看到; 这也就是 scope=BOTH/MEMORY/SPFILE 的区别

oracle 11G 里有 355 个 parameter, 那到底哪个是可以通过 alter session 修改的，哪个是可以通过 alter system 修改的呢？以及那些不可以修改的呢？这个通过 v$system_parameter 就可以知道;

isses_modifiable: 是否能通过 alter session 修改. 这只有两个值: TRUE/FALSE

issys_modifiable: 是否能通过 alter system 修改: 这有三个值: IMMEDIATE/DEFERRED/FALSE (IMMEDIATE 表示立即生效，在当前 session 就生效; DEFFERED: 表示对所有当前正在连接的 session 都不生效，只有对这之后的连接才生效. FALSE 就表示不允许 alter system 修改)

对于 ISSYS_MODIFIABLE=DEFERRED 的参数，必须通过 alter system …. deferred 才能修改; 否则都会报错 ORA-02096

ORA-02096: specified initialization parameter is not modifiable with this option

当使用 ASMM 或者 AMM 的时候，很多 parameter value 都是系统自动调整的，当你修改了某个参数之后又想把这个参数交回给系统自动来管理，那怎么办呢？其实也就是恢复默认值。可以通过 SQL: alter system reset name= value scope=… 注意这里使用的是 RESET 命令。reset 的命令的意思其实就是不设置了，也就是把一个 item 从 spfile 里删除，那么如果要删除，就一定要现有这个 item, 所以如果你要 reset 一个不存在的 parameter 的时候也会报错。或者在 RAC 里，如果设置的时候是 instance by instance 设置的，而 reset 的时候通过 alter system reset … sid= * 也会报错;

SQL select name, value from v$spparameter where name= db_file_multiblock_read_count

NAME VALUE

————————————————– ————————————————–

db_file_multiblock_read_count

在 v$spparameter 里找不到这个 parameter value, 说明没有设置

SQL alter system reset db_file_multiblock_read_count scope=spfile sid= *

alter system reset db_file_multiblock_read_count scope=spfile sid= *

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

这里报错了

这是一种情况, 对于 RAC 来说，你可以 instance by instance 设置参数信息

这里看到每个 instnace 的 parameter value 不一样。即使 value 一样你也可以 instance by instance 设置

SQL select sid, name, value from v$spparameter where name= db_file_multiblock_read_count

SID NAME VALUE

——————————————————————————– ———————————-

racaaweb1 db_file_multiblock_read_count 64

racaaweb2 db_file_multiblock_read_count 256

SQL alter system reset db_file_multiblock_read_count scope=spfile sid= *

alter system reset db_file_multiblock_read_count scope=spfile sid= *

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

这里还是出错了

隐含参数

Oracle 还有另外一种参数，称为隐含参数，隐含参数的名字以下划线_打头。默认情况下隐含参数不会出现在上面的几张表里，除非你修改了这些参数的值;

例子

alter system set _undo_autotune =TRUE;

这个语句修改了隐含参数_undo_autotune 的值; 这里涉及到注意点:

1) 修改隐含参数的时候，隐含参数名字需要用双引号括起来

2) 对于这种后面没带 scope 的，scope 的默认值是 BOTH

3) 对于这种后面没带 sid 的，sid 的默认值是 *

更新之后的值如下:

SQL select name, value from v$spparameter where name= _undo_autotune

NAME VALUE

————————————————– ————————————————–

_undo_autotune TRUE

SQL select name, value from v$system_parameter where name= _undo_autotune

NAME VALUE

————————————————– ————————————————–

_undo_autotune TRUE

也就是说，所有修改过的并且当前生效的参数都可以在 v$parameter 里看到, 不管是隐含的还是非隐含的。那么如何查询哪些没有修改过的隐含参数呢？使用一下 SQL:

Hidden Parameter

set linesize 1000

SET VERIFY OFF

COLUMN parameter FORMAT a37

COLUMN description FORMAT a50 WORD_WRAPPED

COLUMN session_value FORMAT a10

COLUMN instance_value FORMAT a10

SELECT a.ksppinm AS parameter,

a.ksppdesc AS description,

b.ksppstvl AS session_value,

c.ksppstvl AS instance_value

FROM x$ksppi a,

x$ksppcv b,

x$ksppsv c

WHERE a.indx = b.indx

AND a.indx = c.indx

AND a.ksppinm LIKE % ||LOWER(1)|| % ESCAPE /

ORDER BY a.ksppinm;

补充

1: x$ksppi: 这里存的是所有的 parameter name; x$ksppcv 存的是 session level 的 parameter value, x$ksppsv 存的是 instance level 的 parameter value; 这个通过查询 x$fixed_view_definition 就能看到了

2. oracle snapshot 也会记录下这些 parameter value，所以从这里也能看到是否有变化;

3. 当修改任何一个 parameter 的时候，都会在 alert log 里有记录，所以从这里也能看到所有被改变的 parameter;

感谢各位的阅读，以上就是“Oracle 参数的查询和修改方法”的内容了，经过本文的学习后，相信大家对 Oracle 参数的查询和修改方法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！

正文完