| Changing database parameters
|Use the ALTER SYSTEM and ALTER SESSION commands to change database parameter settings.
Changing Database Parameters using ALTER SYSTEM
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.
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;
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
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