JL Computer Consultancy

Alternative scripts for seeing parameter lists (Version 8).

November 2001


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.

Back to Main Index of Topicss


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 
;

Back to Main Index of Topics


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 
;

Back to Main Index of Topics