v$parameter 的说明见这里:
V$PARAMETER displays information about the initialization parameters that are currently in effect for the session.
v$parameter2 的说明见这里:
V$PARAMETER2 displays information about the initialization parameters that are currently in effect for the session, with each list parameter value appearing as a row in the view.
区别在于这句:
with each list parameter value appearing as a row in the view
每个列表参数值在视图中显示为一行
看一个实际的例子。环境为2节点RAC。
select count(*) from v$parameter;
COUNT(*)
----------
458
select count(*) from v$parameter2;
COUNT(*)
----------
463
显然,v$parameter2的行数更多。来看一下多的原因。
第一种可能是,v$parameter2
中有的参数,在v$parameter
中没有。确实找到了3个。
select name from v$parameter2
minus
select name from v$parameter;
NAME
--------------------------------------------------------------------------------
asm_diskgroups
asm_io_processes
asm_power_limit
第二种可能是,在v$parameter
中出现1次的参数,在v$parameter2
中出现了多次。确实找到了2个:
select name, count(*) from v$parameter2 group by name having count(*) > 1;
NAME COUNT(*)
-------------------------------------------------------------------------------- ----------
control_files 2
connection_brokers 2
这样就对上了:
$ bc <<< 458+2+3
463
看一下这几个参数在两个表中的实际值:
SQL> select name, value from v$parameter where name in ('control_files', 'connection_brokers');
NAME VALUE
_____________________ ___________________________________________________________________________________________________
control_files +DATAC1/ORCL/CONTROLFILE/current.692.1155482173, +RECOC1/ORCL/CONTROLFILE/current.623.1155482173
connection_brokers ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))
SQL> select name, value from v$parameter2 where name in ('control_files', 'connection_brokers');
NAME VALUE
_____________________ __________________________________________________
control_files +DATAC1/ORCL/CONTROLFILE/current.692.1155482173
control_files +RECOC1/ORCL/CONTROLFILE/current.623.1155482173
connection_brokers ((TYPE=DEDICATED)(BROKERS=1))
connection_brokers ((TYPE=EMON)(BROKERS=1))
SQL> select name, value from v$parameter where name in ('asm_diskgroups', 'asm_io_processes', 'asm_power_limit');
no rows selected
SQL> select name, value from v$parameter2 where name in ('asm_diskgroups', 'asm_io_processes', 'asm_power_limit');
NAME VALUE
___________________ ________
asm_diskgroups
asm_power_limit 1
asm_io_processes 20
注意:asm的这3个参数,可以登录到ASM实例中查看。