Oracle参数的查询和修改方法

这篇文章主要讲解了“Oracle参数的查询和修改方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“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参数的查询和修改方法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！