Wednesday, March 4, 2015

How to reset spfile parameter value

How to reset parameter value in oracle spfile

The following command can be used reset any parameter specified in spfile. Please note that it will only delete the entry from the spfile but the original value set at instance level will remain the same.

ALTER SYSTEM RESET <parameter_name>;
ALTER SYSTEM RESET <parameter_name> SCOPE=SPFILE SID='INSTANCE_NAME'; (incase of RAC)

SQL> select * from v$spparameter where name like 'processes';

INST_ID SID   NAME       TYPE     VALUE  DISPLAY_VALUE  ISSPEC ORDINAL UPDATE_COMMENT
------- ----- ---------- -------- ------ -------------- ------ ------- --------------
      1 *     processes  integer  300    300            TRUE         1
      1 ORCL1 processes  integer  300    300            TRUE         1
      1 ORCL2 processes  integer  300    300            TRUE         1
 
SQL> alter system reset processes scope=spfile sid='ORCL2';

System altered.

SQL> select * from v$spparameter where name like 'processes';

INST_ID SID   NAME       TYPE     VALUE  DISPLAY_VALUE  ISSPEC ORDINAL UPDATE_COMMENT
------- ----- ---------- -------- ------ -------------- ------ ------- --------------
      1 *     processes  integer  300    300            TRUE         1
      1 ORCL1 processes  integer  300    300            TRUE         1
 
SQL> alter system reset processes scope=spfile sid='ORCL2';

System altered.

SQL> select * from v$spparameter where name like 'processes';

INST_ID SID   NAME       TYPE     VALUE  DISPLAY_VALUE  ISSPEC ORDINAL UPDATE_COMMENT
------- ----- ---------- -------- ------ -------------- ------ ------- --------------
      1 *     processes  integer  300    300            TRUE         1