38 - [Oracle 19C] Default scope in alter system

 

• Alter system set parameter=value

 

- If a server parameter file was used to start up the database, then BOTH is the default.

Alter system set parameter=value same as Alter system set parameter=value scope=both

- If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

Alter system set parameter=value same as Alter system set parameter=value scope=memory

 

 

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT


SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,con_id
from V$PARAMETER
where name='open_cursors';
  2    3
NAME		VALUE		ISSES_MODIFIABLE     ISSYS_MODIFIABLE	  ISPDB_MODIFIABLE	   CON_ID
--------------- --------------- -------------------- -------------------- -------------------- ----------
open_cursors	300		FALSE		     IMMEDIATE		  TRUE				1

 

 

ISSYS_MODIFIABLE(system modifiable) is immediate. This means that I can change the value in the memory level.

ISSES_MODIFIABLE is false that means you cannot change it in the session, but you can change it.

 

 

 

SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$system_PARAMETER
where name='open_cursors';

NAME            VALUE     ISSES_MODIFIABLE  ISSYS_MODIFIABLE  ISPDB_MODIFIABLE  ISDEFAULT  CON_ID
--------------- --------- ----------------- ----------------- ----------------- --------- -------
open_cursors    300       FALSE             IMMEDIATE         TRUE              FALSE           0

 

 

ISDEFAULT is false. This means that this value mentioned in the SP file, you can go to the SP file and you will find this

is already in the SP file.

 

 

SQL> show parameter spfile

NAME        TYPE        VALUE
----------- ----------- ------------------------------------------------------
spfile      string      /u01/app/oracle/product/19/db_1/dbs/spfileorcl.ora

 

 

This mean that the last time I started my database I started using SP file or SQL. so default scope is both.

alter system set open_cursors=301;  --same alter system set open_cursors=301 scope=both;

 

  

SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$system_PARAMETER
where name='open_cursors';

NAME		VALUE		ISSES_MODIFIABLE     ISSYS_MODIFIABLE	  ISPDB_MODIFIABLE     ISDEFAULT     CON_ID
--------------- --------------- -------------------- -------------------- -------------------- --------- ----------
open_cursors	301		FALSE		     IMMEDIATE		  TRUE		       FALSE		  0

 

 

 

 

 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup pfile=/u01/app/oracle/product/19/db_1/dbs/test.ora
ORACLE instance started.

Total System Global Area 1526723608 bytes
Fixed Size		    9135128 bytes
Variable Size		  939524096 bytes
Database Buffers	  570425344 bytes
Redo Buffers		    7639040 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> show parameter spfile

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string
SQL>

 

 

 

SQL> alter system set open_cursors=301 scope=spfile;
alter system set open_cursors=301 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use

 

 

• only memory can be used or alter system set open_cursors=301 which equal to   alter system set open_cursors=301 scope=memory;