Monday, April 23, 2012

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


Enjoy     :-)

1 comment:

Anonymous said...

OK ! got it ! this means that session level parameters can be changes with alter session command right ? and instance level can be changed by alter system command ? but what about making them permanent we also have to include the scope e.g 'scope=both' My question is regarding sga size if i change its size by alter system command do i have to restart the instance so that the new size is allocated or will it be allocated on the fly? I know AMM (automatic memory management takes care of memory allocation to pga and sga etc) thanks