Answer: Check for v$parameter we can find one column ie. ISSYS_MODIFIABLE
set pages 1000
set lines 200
col name for a35
col value for a25
select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter;
1. Parameters basics:
In general, parameter is a placeholder for a variable that contains some value of some type.
We can call it as key-value pair
db_block_siz(key)=8K(value)
db_name(key)=DEVDB(value)
2. How to Identify the parameter as stic or dynamic
SQL> select ISSYS_MODIFIABLE from v$parameter where name like '%utl_file_dir%';
ISSYS_MOD
---------
FALSE=======================================>static
Other examples are:
select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';
select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sessions';
alter system set processes=200 scope=spfile;
alter system set sessions=500 scope=spfile;
3. Check the current value for a parameter processes & sessions
SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';
NAME VALUE ISSES ISSYS_MOD
----------------------------------- ------------------------- ----- ---------
processes 150 FALSE FALSE
SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sessions';
NAME VALUE ISSES ISSYS_MOD
----------------------------------- ------------------------- ----- ---------
sessions 248 FALSE FALSE
4. Try to change staic parameter with scope=both it will fail
SQL> alter system set processes=200 scope=both;
alter system set processes=200 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set sessions=500 scope=both;
alter system set sessions=500 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
5. Try to change static parameter with scope=memory it will fail
SQL> alter system set processes=200 scope=memory;
alter system set processes=200 scope=memory
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set sessions=500 scope=memory;
alter system set sessions=500 scope=memory
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
6. Try to change static parameter with scope=spfile it will be successful
SQL> alter system set processes=200 scope=spfile;
System altered.
SQL> alter system set sessions=500 scope=spfile;
System altered.
7. After changing the parameter re verify the values still pointing to old values since these static parameter takes effect only after database bounce
SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';
NAME VALUE ISSES ISSYS_MOD
----------------------------------- ------------------------- ----- ---------
processes 150 FALSE FALSE
SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sessions';
NAME VALUE ISSES ISSYS_MOD
----------------------------------- ------------------------- ----- ---------
sessions 248 FALSE FALSE
10. Shutdown the database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
11. Start the database
SQL> startup;
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 1023413488 bytes
Database Buffers 536870912 bytes
Redo Buffers 7471104 bytes
Database mounted.
Database opened.
12. Verify the static parameter and now poiting to new values
SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';
NAME VALUE ISSES ISSYS_MOD
----------------------------------- ------------------------- ----- ---------
processes 200 FALSE FALSE
SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sessions';
NAME VALUE ISSES ISSYS_MOD
----------------------------------- ------------------------- ----- ---------
sessions 500 FALSE FALSE
SQL>
13. How to Identify the parameter as static or dynamic
SQL> select ISSYS_MODIFIABLE from v$parameter where name like '%log_archive_dest_2';
ISSYS_MOD
---------
IMMEDIATE====================================>dynamic
Other examples are:
select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='log_archive_dest_2';
select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='open_cursors';
select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='awr_snapshot_time_offset';
alter system set open_cursors=500 scope=both;
alter system set awr_snapshot_time_offset=2 scope=both;
14. Check the current value for a parameter open_cursors & awr_snapshot_time_offset
SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='open_cursors';
NAME VALUE ISSES ISSYS_MOD
----------------------------------- ------------------------- ----- ---------
open_cursors 300 FALSE IMMEDIATE
SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='awr_snapshot_time_offset';
NAME VALUE ISSES ISSYS_MOD
----------------------------------- ------------------------- ----- ---------
awr_snapshot_time_offset 0 FALSE IMMEDIATE
SQL>
15. Try to change these dynamic parameter with scope=both it will be successful
SQL> alter system set open_cursors=500 scope=both;
System altered.
SQL> alter system set awr_snapshot_time_offset=2 scope=both;
System altered.
12. Verify the these dynamic parameter and now pointing to new values immediately without bouncing the database.
SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='open_cursors';
NAME VALUE ISSES ISSYS_MOD
----------------------------------- ------------------------- ----- ---------
open_cursors 500 FALSE IMMEDIATE
SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='awr_snapshot_time_offset';
NAME VALUE ISSES ISSYS_MOD
----------------------------------- ------------------------- ----- ---------
awr_snapshot_time_offset 2 FALSE IMMEDIATE
Please refer the YoutTube video for more information
https://youtu.be/hSntn8Gfedo
Regards,
Mallik
