Reset/Unset Oracle parameters

A parameter value can be reset to the default using the following command

Note: Do not try to change the value of a parameter with” (or) (‘) or (null) to reset it to default

Below example shows, how to reset a oracle parameter. I am running this test on oracle 11gR2(11.2.0.2.0)

SQL> show parameter convert

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------------

db_file_name_convert                 string   +DATA1,+DG_DATA1

log_file_name_convert                string   +LOG,+DG_LOG

SQL> alter system reset db_file_name_convert scope=spfile sid='*';

System altered.

SQL> alter system reset log_file_name_convert scope=spfile sid='*';

System altered.

Resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported. If you proceeding with the parameter change, you will get an error message as below

SQL> alter system reset db_file_name_convert scope=both sid='*';

alter system reset db_file_name_convert scope=both sid='*'

*

ERROR at line 1:

ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported

I tried to change a dynamic parameter which can changed during the run time, even those parameter are not supported in “RESET” with memory or both option

SQL> alter system set log_archive_dest_state_3=DEFER scope=both sid='*';

System altered.

SQL> show parameter log_archive_dest_state_3

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_3             string      DEFER

SQL> alter system reset log_archive_dest_state_3 scope=both sid='*';

alter system reset log_archive_dest_state_3 scope=both sid='*'

*

ERROR at line 1:

ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported

SQL> alter system set log_archive_dest_state_3=ENABLE scope=both sid='*';

System altered.

I am able to change the parameter with out bouncing the database.

SQL> show parameter log_archive_dest_state_3

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_3             string      ENABLE