ITPub博客

首页 > 数据库 > Oracle > oracle的隐含参数或在一般initSID.ora中没有出现的参数的查找办法

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

原创 Oracle 作者:playwawa 时间:2004-12-22 09:45:53 0 删除 编辑
在网上逛的时候碰到了一个我也很想知道的问题的答案,所以贴出来,虽然全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.


System Parameters

rem
rem      Script:          sys_prms.sql
rem      Author:          Jonathan Lewis
rem      Purpose: Reports all system parameters, including the hidden ones
rem
rem      Note:
rem      Must be run by SYS
rem
rem      To be consistent with the Oracle-defined view, the decode for column
rem      is_system_modifiable ought to omit the decode value for 
rem              3 , 'Immediate'
rem
rem      and the decode for column is_modified ought to omit the decode value for
rem              4 , 'System Modified'
 

select 
         nam.indx+1                                  numb,
         nam.ksppinm                                 name,
         val.ksppstvl                                value,
         nam.ksppity                                 type,
         val.ksppstdf                                is_default,
         decode(bitand(nam.ksppiflg/256,1),
                 1,'True',
                   'False'
         )                                           is_session_modifiable,
         decode(bitand(nam.ksppiflg/65536,3),
                 1,'Immediate',
                 2,'Deferred' ,
                 3,'Immediate',
                   'False'
         )                                           is_system_modifiable,
         decode(bitand(val.ksppstvf,7),
                 1,'Modified',
                 4,'System Modified',
                   'False'
         )                                           is_modified,
         decode(bitand(val.ksppstvf,2),
                 2,'True',
                   'False'
         )                                           is_adjusted,
         nam.ksppdesc                                description
from
         x$ksppi          nam,
         x$ksppsv val
where 
         nam.indx = val.indx 
;


Session Parameters

rem
rem      Script:          ses_prms.sql
rem      Author:          Jonathan Lewis
rem      Purpose: Reports all session parameters, including the hidden ones
rem
rem      Note:
rem      Must be run by SYS
rem
select 
         nam.indx+1                                  numb,
         nam.ksppinm                                 name,
         val.ksppstvl                                value,
         nam.ksppity                                 type,
         val.ksppstdf                                is_default,
         decode(bitand(nam.ksppiflg/256,1),
                 1,'True',
                   'False'
         )                                           is_session_modifiable,
         decode(bitand(nam.ksppiflg/65536,3),
                 1,'Immediate',
                 2,'Deferred' ,
                 3,'Immediate',
                   'False'
         )                                           is_system_modifiable,
         decode(bitand(val.ksppstvf,7),
                 1,'Modified',
                 4,'System Modified',
                   'False'
         )                                           is_modified,
         decode(bitand(val.ksppstvf,2),
                 2,'True',
                   'False'
         )                                           is_adjusted,
         nam.ksppdesc                                description
from
         x$ksppi          nam,
         x$ksppcv val
where 
         nam.indx = val.indx 
;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/202861/viewspace-785095/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    105
  • 访问量
    1172996