Oracle views - Notes & tips

mail

V$PARAMETER

Parameters live and change, and can be in different states depending on their "history" :
ISDEFAULT (details)
  • TRUE : the parameter was not set in the parameter file when the instance started (i.e. no configuration has overridden the default value)
  • FALSE : the parameter was set in the parameter file when the instance started
This parameter only reflects the status of the corresponding parameter when the instance started :
  • the parameter may / may not have been altered afterwards
  • ISDEFAULT gives no information about the current value of the parameter (default or not)
ISMODIFIED
  • MODIFIED : the parameter has been modified with ALTER SESSION
  • SYSTEM_MOD : the parameter has been modified with ALTER SYSTEM (affecting all active sessions)
  • FALSE : parameter not modified after instance startup
ISSYS_MODIFIABLE
  • IMMEDIATE : the parameter can be changed with ALTER SYSTEM. The change takes effect immediately.
  • DEFERRED : like IMMEDIATE, but the change affects future sessions only.
  • FALSE : the parameter can not be changed with ALTER SYSTEM.
A diagram I made while working with ISSYS_MODIFIABLE parameters. It helped me realize that ISDEFAULT is often not relevant when dealing with the current value of a parameter.