How to Identify the Static and Dynamic Parameter in Oracle

Sometimes, we may not very sure whether an oracle parameter is static(restarting database is required to come under the action) parameter or dynamic(can be changed without restarting) parameter . We can check this by using the v$parameter2 view which is very similar to v$parameter having few extra rows for long parameters . The another difference between the v$parameter and v$parameter2 is that the format of the output .. For example, if a parameter value say  "x,y"  in V$PARAMETER view does not tell us if the parameter has two values ("x" and "y") or one value ("x, y") whereas V$PARAMETER2 makes the distinction between the list parameter values clear.

SQL> select value from v$parameter WHERE name LIKE 'control_files' ; 


SQL> select value from v$parameter2 WHERE name LIKE 'control_files' ; 

 
Here, If  ISSES_MODIFIABLE  parameter is true, the parameter can be changed on session level , and if  ISSES_MODIFIABLE or ISINSTANCE_MODIFIABLE is true, then parameter can be changed on system level. Here is an example

SQL> SELECT name,Value ,ISSES_MODIFIABLE , ISINSTANCE_MODIFIABLE FROM v$parameter2  WHERE name LIKE '%target%'  ; 

Enjoy     :-)