Forum Stats

  • 3,826,260 Users
  • 2,260,615 Discussions
  • 7,896,858 Comments

Discussions

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

User51642 Yong Huang
User51642 Yong Huang Member Posts: 164 Bronze Badge
edited Mar 12, 2021 6:10PM in Database Ideas - Ideas

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


User51642 Yong HuangSven W.Rostislav KushnirenkoJose Carlos Pavón
4 votes

Active · Last Updated

Comments