By querying V$parameter one can find if the parameter can be modified in session and/or system.

If isses_modifiable is true then value can be modified in session, if FALSE then it can’t be modified using ALTER SESSION SET..

If issys_modifiable = DEFERRED, the value can be changed with scope=spfile specified (if spfile is used) and the change will take into effect into subsequent sessions.

If issys_modifiable =FALSE, the value can be changed using ALTER SYSTEM only if spfile is used and the change will take into effect after instance restart.

If issys_modifiable = IMMEDIATE, the value can be changed with scope=[memory|both|spfile]

To find out if the database is using spfile is being used refer to the following link http://atomic-temporary-2592919.wpcomstaging.com/2008/06/22/how-to-tell-if-the-database-was-started-with-spfile-or-pfile/

Eg:

SELECT  isses_modifiable, issys_modifiable FROM V$parameter WHERE name = ‘shared_pool_size’;

ISSES   ISSYS_MOD

—– ———
FALSE  IMMEDIATE