v$parameter Dynamic Performance View in Oracle 19c

Overview of `v$parameter` View in Oracle 19c.
Purpose:

• v$parameter is a dynamic performance view in Oracle databases (including Oracle 19c), used primarily by DBAs to retrieve information about the current values of initialization parameters.
• Usage:
  • Provides current session or system-wide parameter settings.
  • Helps to determine if parameters are dynamic, modifiable at runtime, or require a database restart.
• Important columns include:
  • name – Parameter name.
  • value – Current value of the parameter.
  • isdefault – Indicates if the parameter is using the default value.
  • isses_modifiable – Shows if the parameter can be modified at the session level (TRUE/FALSE).
  • issys_modifiable – Indicates if the parameter can be dynamically modified (IMMEDIATE) or requires restart.
  • ismodified – Indicates if the parameter was modified after instance startup.

Example Query
Here’s how to use this view to query parameter values in Oracle 19c:

-- Retrieve information about the open_cursors parameter
SELECT name, value, isdefault, issys_modifiable, ismodified
FROM v$parameter
WHERE name = 'open_cursors';

Sample output:

NAME            VALUE    ISDEFAULT  ISSYS_MODIFIABLE  ISMODIFIED
--------------- --------- ---------- ------------------- -----------
open_cursors     300      FALSE     IMMEDIATE           FALSE

Practical Use Cases

• Quickly check current database configuration parameters.
• Validate runtime changes to parameters.
• Identify non-default parameter values for troubleshooting or tuning.

Conclusion

• v$parameter is widely utilized by DBAs and developers for performance tuning, troubleshooting, and verifying the runtime configuration of Oracle Database 19c environments.
• It remains one of Oracle’s core dynamic views in 19c, fully supported and actively used for database administration tasks.

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 diagram describes the columns in the v$parameter view:

v$parameter Characteristics

NUM	A number that Oracle uses to identify the parameter internally.
NAME	The name of the parameter.
TYPE	A number representing the parameter type: 1=boolean, 2=string, 3=integer.
VALUE	The current value of the parameter.
ISDEFAULT	This is TRUE if the parameter's value represents the default setting. Otherwise, this is FALSE.
ISSES_MODIFIABLE	This is TRUE if the parameter can be changed for a single user session using the ALTER SESSION command. Otherwise, this is FALSE.
ISSYS_MODIFIABLE	This is either IMMEDIATE, DEFERRED, or FALSE. IMMEDIATE means that the parameter can be changed using ALTER SYSTEM, and the change takes effectimmediately. DEFERRED means that a change takes effect only for future user connections. FALSE means that the parameter cannot be changed by usingALTER SYSTEM.
ISMODIFIED	Indicates whether a parameter was modified. A value of MODIFIED indicates that ALTER SESSION was used to modify the parameter. A value ofSYS_MODIFIED indicates that ALTER SYSTEM was used to modify the parameter. A value of FALSE indicates that the parameter's value has not beenmodified from that specified in the initialization file.
ISADJUSTED	Indicates whether the Oracle software adjusted a parameter setting that you specified. TRUE means that the parameter value was adjusted. FALSEmeans that it wasn't.
DESCRIPTION	A short comment describing the parameter's purpose.

Not all parameters can be modified with an ALTER SYSTEM statement, but you can check to see if a statement is alterable by looking at the v$parameter view. You can see if any parameter can be changed with an ALTER SYSTEM statement by looking at the issys_modifiable column in the v$parameter view.

SQL> desc v$parameter

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.