Reset the instance parameter to default value in Oracle

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.

SQL> show parameter _optimizer_enable_extended_Stats

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

SQL> ALTER SYSTEM RESET "_optimizer_enable_extended_stats";
System altered.

SQL> show parameter _optimizer_enable_extended_Stats

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

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 )

Google photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.