How to delete/reset a parameter in spfile using ALTER SYSTEM?

Using ALTER SYSTEM RESET one can delete parameter from spfile if the parameter is in the spfile.

— shows the parameter is in the spfile
$ pwd
$ strings spfileTEST.ora | grep open

— Login to SQL*Plus, scope has to spfile and sid has to be supplied. When '*' is specified it applies to all instances if it is a cluster
SQL> alter system reset open_cursors scope=spfile sid='*';

System altered.

# check spfile after running reset command grep no longer finds the parameter in the spfile
$ strings spfileTEST.ora | grep open
$ echo $?

Note: If the parameter is not found in the spfile oracle will return “ORA-32010: cannot find entry to delete in SPFILE”, and if the scope is set to both oracle will return “ORA-32009: cannot reset the memory value for instance * from instance TESTDB”

Leave a Reply

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

You are commenting using your 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