Controlfile DB Parameters   «Prev  Next»
Lesson 8The v$parameter dynamic performance view
ObjectiveQuery the v$parameter view to learn about your database parameter settings.

v$parameter dynamic performance view

The SHOW PARAMETER command is useful as far as it goes, but you can learn even more about your database parameter settings by querying the v$parameter view. The v$parameter view not only tells you the values of current parameter settings, it also provides other useful information such as whether you can modify a parameter and whether or not any given parameter setting represents the default value for that parameter.
The following mouseover describes the columns in the v$parameter view:
internal parameter
  1. A number that Oracle uses to identify the parameter internally.
  2. The name of the parameter.
  3. A number representing the parameter type: 1=boolean, 2=string, 3=integer.
  4. The current value of the parameter.
  5. This is TRUE if the parameter's value represents the default setting. Otherwise, this is FALSE.
  6. This is TRUE if the parameter can be changed for a single user session using the ALTER SESSION command. Otherwise, this is FALSE.
  7. This is either IMMEDIATE, DEFERRED, or FALSE. IMMEDIATE means that the parameter can be changed using ALTER SYSTEM, and the change takes effect immediately. DEFERRED means that a change takes effect only for future user connections. FALSE means that the parameter cannot be changed by using ALTER SYSTEM.
  8. Indicates whether a parameter was modified. A value of MODIFIED indicates that ALTER SESSION was used to modify the parameter. A value of SYS_MODIFIED indicates that ALTER SYSTEM was used to modify the parameter. A value of FALSE indicates that the parameter's value has not been modified from that specified in the initialization file.
  9. Indicates whether the Oracle software adjusted a parameter setting that you specified. TRUE means that the parameter value was adjusted. FALSE means that it wasn't.
  10. A short comment describing the parameter's purpose.

v$parameter Characteristics
You can see that the v$parameter view provides you with a wealth of information about your database parameter settings. The following query, for example, returns a list of all parameters that you can modify at the system level while the database is running: 
SELECT name

FROM v$parameter

WHERE issys_modifiable <> 'FALSE';

Any of the parameters returned by this query can be modified on the fly using the ALTER SYSTEM command.
Learn how to modify parameter settings using the ALTER SYSTEM and ALTER SESSION commands in the next lesson.