Reset the instance parameter to default value in Oracle

If you want to reset the default value then RESET command only effect with restart because it remove the entry from the SPFILE.
When you restart the database and automatic reset to default value because it removed from spfile.

Verify with following SQL which is default value set for parameter

select name,value,isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated
from v$parameter;


RESET COMMAND for parameter at instance level

ALTER SYSTEM RESET parameter_name;

Note: It effect in next startup.

You need to manually modified the parameter with ALTER command

ALTER SYSTEM SET parameter_name= VALUE SCOPE=BOTH;


Example of RESET the parameter in Oracle
Note: If you again RESET the parameter then it will give error because it already removed from spfile.
Its means RESET command only work after restart the database.

SQL> ALTER SYSTEM SET "_optimizer_enable_extended_stats"= FALSE scope=both;
System altered.

NAME                                 TYPE         VALUE
------------------------------------ ------------ ----------
_optimizer_enable_extended_stats     boolean      FALSE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
_optimizer_enable_extended_stats     boolean     FALSE

SQL> ALTER SYSTEM RESET "_optimizer_enable_extended_stats";
ALTER SYSTEM RESET "_optimizer_enable_extended_stats"
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE