Did You Know #4 – spfile

Sometimes we need to delete an entry from our spfile.I saw in the past DBAs that don’t know how to do that, so they create a pfile from spfile, remove the entry and recreate the spfile. Others just change the value to the default value.
If you don’t know how to do it, this is the way (in this example, delete the optimizer_index_cost_adj parameter):

alter system reset optimizer_index_cost_adj scope=spfile sid='*';

Good luck,
Liron
Update: It’s worth mentioning that the “reset” command works only when writing to the spfile. There is no way to change a parameter in memory and then remove it from the memory. In order to do this, you have to change the parameter in memory to the previous (default) value.
thanks Franck Pachot for the comment on Twitter