Modifying Initialization Parameters in Oracle

Back to: Oracle DBA Tutorials

Modifying Initialization Parameters in Oracle

In this article, I am going to discuss Modifying Initialization Parameters in Oracle with Examples. Please read our previous article where we discussed Types of Initialization Parameters in Oracle with Examples.

Modifying Initialization Parameters in Oracle

So, until now we understood what is the meaning of the initialization parameter. We understand Parameter files and the benefits of parameters. Now we will try to understand the concept of modifying initialization parameters. So, if we need to make changes in the database, we need to modify the parameters.

Modifying parameters is to set capacity limits or improve performance. So, this is the main reason to change the parameters. We use EM Express (Enterprise Manager Express), and it is a nice tool, and we will understand this further. We can use SQL*Plus to modify the parameters. We have ALTER SESSION and ALTER SYSTEM. Alter session will modify only a specific session. Alter system will modify the whole system.

How Can We Modify the Database?

we have a view named V$Parameter. We have three important columns in the V$Parameter view.

1. Session level Changes (ISSES_MODIFIABLE column)
2. System level Changes (ISSYS_MODIFIABLE column)
3. PDB level Changes (ISPDB_MODIFIBALE column)

Now for example, if the value for the decision is true, this means that we can change that parameter based on the current session. If we find it true in the column, is PDB more deferrable, this will mean that we can change the parameter according to the pluggable database, so we can understand that we have many pluggable and each pluggable has different parameters. So, we have some parameters that can be different from pluggable to pluggable. There is a certain process to changing the parameter.

1. We have to query the V$Parameter.
2. We have to understand the three columns
3. After understanding the three columns then we have to change the parameter according to the column.

Note: In the ALTER SYSTEM command we have a clause called Scope, and this is very important and we have many options for the scope.

1. MEMORY
2. SPFILE
3. BOTH
4. DEFERRED

Memory: We can run a command with alter system and change the parameter on memory scope. This indicates that the changes are made in memory, only take effect immediately, and persist until that database is shut down. For Example, when you change a parameter and the Scope is memory, these changes happen in their memory. Only if you switch off your database and open it again, these changes will be no longer available.

SP File: This scope indicates that the change is made on the Server parameter file. The new changes will affect only when the database is next, shut down and start up again. Actually, we have some parameters. We cannot change it except in the S.P. file. So, you have to change it before and then you have to restart the database again.

BOTH: We have both and this is the default. So, if you make alter system and you did not specify memory or S.B file, the default will be both. And this indicates that the change is made in memory and in the server parameter file and this is the default.

DEFERRED: We also have deferred and this means effective for future decisions. So, this means that you can make alter and you will use the option deferred, but only the future decision will take these changes.

Note: There are a few examples defining these parameters, we will discuss this later.

In the next article, I am going to discuss Alter Session in Oracle with Examples. Here, in this article, I try to explain Modifying Initialization Parameters in Oracle with Examples and I hope you enjoy this Modifying Initialization parameter in Oracle article.