| Lesson 9 | Changing database parameters |
| Objective | Use the ALTER SYSTEM and ALTER SESSION commands to change database parameter settings. |
Changing Database Parameters using ALTER SYSTEM
Using the ALTER SYSTEM and ALTER SESSION commands, you can
change database parameter settings while the database is running. Why would you want to do that? Primarily because you may not want to bring the entire system down, forcing all the users to disconnect, just to change a setting. The users probably wouldn't appreciate that.
The ALTER SYSTEM command allows you to change a parameter setting for the system as a whole. The ALTER SESSION command allows you to change a parameter setting for only your current session.
The ALTER SYSTEM statement is a privileged operation usually issued by the DBA or someone with SYSDBA privileges.
Not all parameters can be modified with an ALTER SYSTEM statement, but you can check to see if a statement is alterable by looking at the v$parameter view. You can see if any parameter can be changed with an ALTER SYSTEM statement by looking at the issys_modifiable column in the v$parameter view.
SQL> desc v$parameter
As an example, if you as the DBA decide that you want to suspend the enforcement of resource limits temporarily, you could issue the following command:
ALTER SYSTEM SET resource_limit = FALSE;
If a user needed to execute a very large sort and that user held the ALTER SESSION privilege, then he or she could increase the sort
area size using a command like this:
ALTER SESSION SET sort_area_size = 10000000;
Note:
Most of the settings that can be changed using ALTER SESSION are relatively innocuous, and affect only one user. That is not true, however, of the sort_area_size setting. If you set sort_area_size to an extremely high value, a single user can use up all available memory and bring your database to its knees. If you give a user the ALTER SESSION privilege, make certain that the user understands this, and be sure to give the user an acceptable range of values for the sort_area_size setting.
To issue the ALTER SYSTEM command, you need the ALTER SYSTEM system privilege. Likewise, to issue the ALTER SESSION command, you need the ALTER SESSION system privilege. Not all parameters can be modified. The SQL reference manual lists modifiable parameters for each command. You can also query the v$parameter view to find out which parameters are modifiable and at what level. We will wrap up the module on managing control files in the next lesson.
Managing Database Parameters - Quiz