How to Change the Server Parameters Value

Server Parameters:

Oracle Server parameters allow you to modify many aspects of the Oracle server.

To Check the current value of parameter:

SQL>  select name,value from v$parameter where name='Some_Parameter';

Changing a Parameter value:

Use the ALTER SYSTEM command to set parameters:

1. ALTER SYSTEM set parameter = value SCOPE = MEMORY;
2. ALTER SYSTEM set parameter = value SCOPE = SPfile;
3. ALTER SYSTEM set parameter = value SCOPE = BOTH;

Where

• MEMORY - This affects the database now; but will not remain after a restart. 

• SPfile - This does not change the instance immediately, but will modify the SPfile takes effect after a restart.

• BOTH - change both the current instance and the spfile.

SYSTEM and SESSION:

=>Some parameters can be modified immediately with ALTER SYSTEM, 

=>Some may only be modified for a single session with ALTER SESSION. 

=>Static parameters must be modified with scope=SPfile

V$PARAMETER:

Column                                         Datatype                                          Description

=========                            =============                             =============

ISSES_MODIFIABLE              VARCHAR2(5)            Indicates whether the parameter can be                                                                                                    changed with ALTER SESSION (TRUE)                                                                                                or not (FALSE)

ISSYS_MODIFIABLE            VARCHAR2(9)              Indicates whether the parameter can be                                                                                                    changed with ALTER SYSTEM and when                                                                                              the change takes effect:

IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.

DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.

FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.

Acton Plan:

===========

SQL> show parameter utl

SQL> alter system utl_file_dir='path_name' scope=spfile/both;