Check Dynamic or Static Parameter in Oracle

Check dynamic or Static Parameters in Oracle:

In the V$PARAMETER view, Column ISSYS_MODIFIABLE having the FALSE value are STATIC parameter otherwise its DYNAMIC Parameter in Oracle Database.

select name, value, issys_modifiable from v$parameter;

Dynamic Parameters are those parameters that do not need to restart the database after changing its value, it comes in immediate effects. We can do it by ALTER SYSTEM Command,

ALTER SYSTEM SET log_archive_dest=’/u01/arch’ SCOPE=both;
OR
ALTER SYSTEM SET log_archive_dest='/u01/arch' SCOPE=memory;

Static Parameters are those parameters that need to reboot the database to change its value in effects. We can do it with ALTER SYSTEM and bounce the Database instance.

ALTER SYSTEM SET proecesses=500 SCOPE=spfile;
Shutdown immediate
Startup