Oracle 初始化参数的管理

最新推荐文章于 2023-12-25 17:15:24 发布

longgangbai

最新推荐文章于 2023-12-25 17:15:24 发布

阅读量135

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/longgangbai/article/details/84010800

版权

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
from	v$parameter
where	isdefault = 'FALSE'
and	value is not null
order	by name
/

以name=value形式显示参数信息
set pages 999 lines 100
select 	name || '=' || decode(type, 2, '''') || value 
	|| decode(type, 2, '''') parameter
from	v$parameter
where	isdefault = 'FALSE'
and	value is not null
order	by name
/

重置初始化参数(当使用不推荐过期参数,oracle报错的时候,可以使用这个语句重置)
alter system reset <parameter> scope=spfile sid='*'
/