Alter system set|reset____oracle in Oracle

1. Specifying scope at set and reset is preceded by a specified SID parameter, otherwise an error occurs.

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. Sid must be specified when reset is used, otherwise there will be an error

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. When the reset is used, the SID cannot use if scope is Memory,both

Sql> alter system set OPEN_CURSORS=500 Scope=both;

System altered.

Sql> Show Parameter Open_cursors

name                                  type         VALUE
----------------------------------------------------------------------- ------
open_cursors                          integer    
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 * 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.