oracle的隐含参数或在一般initSID.ora中没有出现的参数的查找办法

最新推荐文章于 2023-10-24 13:12:35 发布

csdn151023

最新推荐文章于 2023-10-24 13:12:35 发布

阅读量234

点赞数

文章标签：数据库

在网上逛的时候碰到了一个我也很想知道的问题的答案，所以贴出来，虽然全english的可是看起来也不是很空难，不管怎样的确是一片不错的文章![@more@]

JL Computer Consultancy

Alternative scripts for seeing parameter lists (Version 8).

Update: 23rd November 2001 - I received a note from Jaap W. van Dijk pointing out that there is a perfectly good description of the column is_adjusted in the 8.1.5 reference manual - and when I looked, I discovered it was even in the 8.0.5 reference manual; so I've added it in below.

Since version 7.3, the v$parameter internal monitoring view has undergone a significant change. The ever-increasing list of parameters which could be adjusted at the session level required Oracle Corp. to start distinguishing between the system level settings of the parameters and the local values of the parameters. Consequently the basis of the original v$parameter view was changed, and a new v$system_parameter view introduced.

Pre-7.3, v$parameter was a simple select on x$ksppi

Post-7.3, x$ksppi was used to hold the names (and descriptions) of the parameters, and two extra x$ views were introducedd: x$ksppcv (current values - corresponding to v$parameter), and x$ksppsv (system values - corresponding to v$system_parameter).

In version 8.0 the views were modified slightly so that 'hidden' parameters which had been set would become visible in the v$parameter view (even though they are hidden in the v$ystem_parameter view).

An anomaly is that the version 8 views allow v$parameter to report some parameters (such as spin_count) as immediately system modifiable whereas v$system_parameter reports them as NOT system modifiable.

The following two scripts (which are virtually identical) allow the SYS id to see the current values of ALL the parameters, including the 'hidden' parameters which are normally kept hidden by the simple expedient of excluding all the parameters whose name start with '_'. The scripts also show whether or not the parameters can be changed at the session or system levels, whether they have been changed, and (for system level changes) whether the change would have an immediate effect or only affects subsequent connections.

You could of course modify the scripts to 'create or replace view all_(system_)parameters as ...' if you feel like letting users other than SYS see them.

A Brief Note on the Newer Columns:

is_system_modifiable

Has the values FALSE, IMMEDIATE, DEFERRED. If you alter an immediate parameter then all currently active sessions will be affected. If you alter a deferred parameter (and you need to add the DEFERRED keyword to the end of the ALTER SYSTEM command), then current sessions will not be affected, but new sessions will see the change. You cannot defer parameter changes where this flag is set to IMMEDIATE.

is_session_modifiable

Has the values TRUE or FALSE. If you have the ALTER SESSION privilege, then you can change any parameters where this column is TRUE. The change is immediate, but for that single session only. (If you issue a connect command, the change disappears as the new session starts).

is_modified

If you change a parameter using alter session, this changes to MODIFIED in v$parameter. If it is system modifiable and immediate, then if someone else changes it using alter system this column will read SYSTEM MODIFIED in your v$parameter but only MODIFIED in your v$system_parameter.

is_adjusted

Quoted from the reference manual:

"Indicates that the rdbms adjusted the input value to a more suitable value (e.g., the parameter value should be prime, but the user input a non-prime number, so the rdbms adjusted the value to the next prime number)"

Having said that, I've still not found a circumstance where this becomes TRUE - however, the cases where I've tested it under Oracle 8.1.7, the value visible in v$parameter does not change, even though Oracle adjusts the size of an array in the SGA in the manner indicated by the reference manual.