Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Make "both" the default scope for "alter system reset"

User51642 Yong HuangMar 12 2021 — edited Mar 12 2021

In spite of the claim in documentation (regardless version) "Refer to the set_parameter_clause to learn about the parameters you can reset, and for the full semantics of the SCOPE and SID clauses", the SQL statement "alter system reset" only removes the setting in spfile, not in memory. So it behaves differently from "alter system set". Here's a test in Oracle 19.8:

SQL> sho parameter _subquery_pruning_enabled
SQL> select value from v$parameter where name='_subquery_pruning_enabled';

no rows selected

SQL> sho parameter spfile

NAME                                TYPE       VALUE
------------------------------------ ----------- ------------------------------
spfile                              string     +DATA/DBTEST/PARAMETERFILE/spf
                                                ile.272.1042821489
SQL> alter system set "_subquery_pruning_enabled"=false;

System altered.

SQL> select value from v$parameter where name='_subquery_pruning_enabled';

VALUE
------------------------------------------------------------------------------------------------------------------------------------
FALSE

SQL> select value from v$spparameter where name='_subquery_pruning_enabled';

VALUE
------------------------------------------------------------------------------------------------------------------------------------
FALSE

SQL> alter system reset "_subquery_pruning_enabled";

System altered.

SQL> select value from v$parameter where name='_subquery_pruning_enabled';

VALUE
------------------------------------------------------------------------------------------------------------------------------------
FALSE <-- this should be gone, ideally or according to documentation

SQL> select value from v$spparameter where name='_subquery_pruning_enabled';

no rows selected <-- good, as expected

SQL> alter system reset "_subquery_pruning_enabled" scope=memory; <-- scope=both would work too

System altered.

SQL> select value from v$parameter where name='_subquery_pruning_enabled';

no rows selected <-- now gone
Comments
Post Details
Added on Mar 12 2021
4 comments
1,039 views