Oracle教程 2023-02-24

Oracle 视图 V$PARAMETER 官方解释，作用，如何使用详细说明

本站中文解释

Oracle 视图 V$PARAMETER 是一个内部系统视图，它提供了一个容易访问 Oracle 使用的全部参数/变量配置信息。它将Oracle参数分成3类：

1. 全局参数：这些参数可以影响整个数据库，应用程序可以访问V$PARAMETER视图来查看或使用这些参数来调整其行为。

2. SESSION参数：这些参数是为当前SESSION准备的，它们可以被变更不会影响其他SESSION。

3. 实例参数：这些参数会影响当前实例，应用程序也可以查看这些参数并根据它们定义的最大值来调整服务端的行为。

使用V$PARAMETER视图可以提供很多实用的信息，比如，对内存大小，共享内存分配，锁定和其他内部参数，这些参数的取值范围都来自V$PARAMETER的内容。它也是一种很好的性能分析工具，它可以用来帮助我们查看Oracle参数的状态，帮助我们分析出可能的性能瓶颈。

官方英文解释

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`.
`DEFAULT_VALUE`	`VARCHAR2(255)`	The default value for this parameter. This is the value of the parameter if a value is not explicitly specified for the parameter.
`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`). The database sets the value of the `ISDEFAULT` column to `TRUE` for parameters that are not specified in the init.ora or server parameter file (SPFILE).
`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.
`ISPDB_MODIFIABLE`	`VARCHAR2(5)`	Indicates whether the parameter can be modified inside a PDB (`TRUE`) or not (`FALSE`)
`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
`CON_ID`	`NUMBER`	The ID of the container to which the data pertains. Possible values include: `0`: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. `1`: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data

Examples

The following query returns the default value for the ALLOW_GLOBAL_DBLINKS initialization parameter:

SQL> SELECT name, default_value FROM v$parameter
  2  WHERE name = 'allow_global_dblinks';

NAME
-----------------------------------------------------------
DEFAULT_VALUE
----------------------------------------------------------- 
allow_global_dblinks
FALSE


SQL>

The following query shows that the ALLOW_GLOBAL_DBLINKS initialization parameter is not modifiable in a PDB:

SQL> SELECT name, ispdb_modifiable FROM v$parameter
  2  WHERE name = 'allow_global_dblinks';

NAME
-----------------------------------------------------------
ISPDB
-----
allow_global_dblinks
FALSE


SQL>

本站中文解释

官方英文解释

相关推荐