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.
| 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 | I haven't yet found a circumstance where this becomes TRUE - if you discover one, please let me know. |
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 ;
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 ;