Change Instance parameter

In today’s article we will be explaining how to do Instance Level Parameter Change.

This change applies to all user sessions.

ALTER SYSTEM SET command is used.

There is no obligation for the SYS user to make this change. Any user with ALTER SYSTEM privilege can do it.

SQL SENTENCES

Some of the parameters in the database are dynamic and some are static.

Dynamic parameters: parameters that we can change while our database is open and apply immediately.

Static parameters: parameters that need to be closed and opened in order for the database to be valid, which are not valid immediately after being changed.

In this context;

SQL> ALTER SYSTEM SET PARAMETER_NAME=new_value; It both permanently writes the value of the parameter to the SPFILE parameter file and changes it on MEMORY so that it is applied in the open/running database.

SQL> ALTER SYSTEM SET PARAMETER_NAME=new_value SCOPE=BOTH; It both permanently writes the value of the parameter to the SPFILE parameter file and changes it on MEMORY so that it is applied in the open/running database.

SQL> ALTER SYSTEM SET PARAMETER_NAME=new_value SCOPE=SPFILE; The change is only saved in the SPFILE parameter file. It does not apply to the currently open/running database. Our database must be closed and opened for it to be valid.

SQL> ALTER SYSTEM SET PARAMETER_NAME=new_value SCOPE=MEMORY; The change is applied only on MEMORY and ensures that it is applied in the open/running database.

For example;

When run, it will get an error as below. The reason is that this parameter is a static parameter.

So if we only want changes to SPFILE