About Parameter

1.Parameters are specified in currently used by Instance SPFILE.

SQL> select name, value
2 from v$spparameter
3 where isspecified = 'TRUE';

2.Parameters are modifiable through "ALTER SESSION".

SQL> select name, value
2 from v$parameter
3 where isses_modifiable = 'TRUE' ;

3.Parameters are not modifiable through "ALTER SYSTEM"

SQL> select name, value
2 from v$parameter
3 where issys_modifiable = 'FALSE';

4.Parameters are modifiable through "ALTER SYSTEM" and Take Immediate Effect.( if instance startup through spfile).

SQL> select name, value
2 from v$parameter
3 where issys_modifiable = 'IMMEDIATE';

5.Parameters are modifiable through "ALTER SYSTEM + DEFERRED" and take effect in subsequent session.

SQL> col name format a20
SQL> col value format a40
SQL> select name, value
2 from v$parameter
3 where issys_modifiable = 'DEFERRED';

NAME VALUE
-------------------- ----------------------------------------
backup_tape_io_slaves TRUE
object_cache_optimal_size 102400
object_cache_max_size_percent 10
sort_area_size 65365
sort_area_retained_size 0
olap_page_pool_size 0
6 rows selected.
SQL> alter system set sort_area_size = 65600 deferred;
System altered.
SQL> show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------sort_area_size integer 65365
SQL> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> conn sys/oracle@orcl1 as sysdba
Connected.
SQL> show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------sort_area_size integer 65600