alter system set|reset

1。 在set和reset 的时候先指定scope，在指定sid参数前面，否则会出错。

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

SQL> alter system set open_cursors=400 sid='*' scope=both;
alter system set open_cursors=400 sid='*' scope=both
                                          *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> alter system set open_cursors=400 scope=both sid='*';

System altered.

SQL> alter system reset open_cursors;
alter system reset open_cursors
                              *
ERROR at line 1:
ORA-00905: missing keyword

SQL> alter system reset open_cursors scope=both;
alter system reset open_cursors scope=both
                                         *
ERROR at line 1:
ORA-00905: missing keyword

SQL> alter system reset open_cursors sid='*' scope=both;
alter system reset open_cursors sid='*' scope=both
                                        *
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> alter system reset open_cursors scope=both sid='*';
alter system reset open_cursors scope=both sid='*'
*
ERROR at line 1:
ORA-32009: cannot reset the memory value for instance * from instance orcl

SQL> alter system reset open_cursor sid='*' scope=spfile;
alter system reset open_cursor sid='*' scope=spfile
*
ERROR at line 1:
ORA-00922: missing or invalid option

SQL> alter system reset open_cursors scope=spfile sid='*';

System altered.

2。 使用reset时必须指定sid，否则会出错

SQL> alter system set open_cursors=1000 scope=both;

System altered.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     1000
SQL> alter system reset open_cursors scop=spfile;
alter system reset open_cursors scop=spfile
                                *
ERROR at line 1:
ORA-00905: missing keyword

SQL> alter system reset open_cursors scope=spfile sid='orcl';
alter system reset open_cursors scope=spfile sid='orcl'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

SQL> alter system reset open_cursors scope=spfile sid='*';

System altered.

SQL> alter system reset open_cursors scope=memory sid='orcl';

System altered.

3。在使用reset的时候如如果scope为memory,both的时候sid不能使用*

SQL> alter system set open_cursors=500 scope=both;

System altered.

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500
SQL> alter system reset open_cursors scope=both sid='*';
alter system reset open_cursors scope=both sid='*'
*
ERROR at line 1:
ORA-32009: cannot reset the memory value for instance * from instance orcl

SQL> alter system reset open_cursors scope=memory sid='*';
alter system reset open_cursors scope=memory sid='*'
*
ERROR at line 1:
ORA-32009: cannot reset the memory value for instance * from instance orcl

SQL> alter system reset open_cursors scope=memory sid='orcl';

System altered.