Oracle v$parameter表知多少

kxjiaddbyc

　　orale中一个重要的视图v$parameter,我们知道知道多少呢?官方解释如下:
　　V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.


Column
Datatype
Description

NUM
NUMBER
Parameter number


NAME
VARCHAR2(80)
Name of the parameter


TYPE
NUMBER
Parameter type:

• 
  1 - Boolean
  
  
• 
  2 - String
  
  
• 
  3 - Integer
  
  
• 
  4 - Parameter file
  
  
• 
  5 - Reserved
  
  
• 
  6 - Big integer
  
  

VALUE
VARCHAR2(4000)
Parameter value for the session (if modified within the session); otherwise, the instance-wide parameter value


DISPLAY_VALUE
VARCHAR2(4000)
Parameter value in a user-friendly format. For example, if the VALUE column shows the value 262144 for a big integer parameter, then the DISPLAY_VALUE column will show the value 256K.


ISDEFAULT
VARCHAR2(9)
Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)


ISSES_MODIFIABLE
VARCHAR2(5)
Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)


ISSYS_MODIFIABLE
VARCHAR2(9)
Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:

• 　　IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.
  
  
• 　　DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.
  
  
• 　　FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.
  
  

ISINSTANCE_MODIFIABLE
VARCHAR2(5)
For parameters that can be changed with ALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.


ISMODIFIED
VARCHAR2(10)
Indicates whether the parameter has been modified after instance startup:

• 　　MODIFIED - Parameter has been modified with ALTER SESSION
  
  
• 　　SYSTEM_MOD - Parameter has been modified with ALTER SYSTEM (which causes all the currently logged in sessions' values to be modified)
  
  
• 　　FALSE - Parameter has not been modified after instance startup
  
  

ISADJUSTED
VARCHAR2(5)
Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)


ISDEPRECATED
VARCHAR2(5)
Indicates whether the parameter has been deprecated (TRUE) or not (FALSE)


ISBASIC
VARCHAR2(5)
Indicates whether the parameter is a basic parameter (TRUE) or not (FALSE)


DESCRIPTION
VARCHAR2(255)
Description of the parameter


UPDATE_COMMENT
VARCHAR2(255)
Comments associated with the most recent update


HASH
NUMBER
Hash value for the parameter name


　　
　　

Oracle 初始化参数的管理
查看修改的参数
select name
,      value
from   v$parameter
where  ismodified != 'FALSE'
/

查看过期不推荐使用的参数
select name
,      value
from   v$parameter
where  isdeprecated = 'TRUE'
/

查看非默认的参数
set pages 999 lines 100
col name format a30
col value format a50
select name
,value
fromv$parameter
whereisdefault = 'FALSE'
andvalue is not null
orderby name
/
以name=value形式显示参数信息
set pages 999 lines 100
select name || '=' || decode(type, 2, '''') || value
|| decode(type, 2, '''') parameter
fromv$parameter
whereisdefault = 'FALSE'
andvalue is not null
orderby name
/
重置初始化参数
alter system reset <parameter> scope=spfile sid='*'
/