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

Advertisements

One thought on “Did You Know #4 – spfile

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s