Types of Initialization Parameters in Oracle

Back to: Oracle DBA Tutorials

Types of Initialization Parameters in Oracle

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

Types of Initialization Parameters in Oracle

This is the most important topic in the oracle database. Knowing about these initialization parameters will help everyone understand the database better and make daily work easier. We will understand initialization parameters. We have three types of initialization parameters. There are three types of parameters, they are as follows:

1. Basic Parameters
2. Advanced Parameters
3. Derived Parameters

Basic Parameters in Oracle:

Most databases should only need to have the database’s basic initialization parameters set to run properly and efficiently. We have hundreds of parameters in the database but there are a few basic parameters that we use and follow during our daily tasks. Basic parameters are around 30 parameters. Most of the DBA do not even use all of these 30 parameters during their tasks. We have operating system-dependent parameters: These parameters are also both basic and advanced parameters.

Example: DB_BLOCK_SIZE

Advanced Parameters in Oracle:

Even the expert DBA will not know about the advanced parameters. No need to understand each and every parameter at the beginning. Sometimes we need to change the parameter to enhance the performance. Some of these parameters require expert DBAs. Even the oracle itself suggests we should not touch the advanced parameters.

Example: DB_CACHE_SIZE

Derived Parameters in Oracle:

The Derived parameters are the parameters that are calculated from the values of other parameters. The Derived parameters are neither Basic parameters, nor advanced parameters.

Example: sessions derived from processes.

SESSION= (1.5 * PROCESSES) + 22

Example: DB_BLOCK_SIZE, SGA_TARGET, CONTROL_FILES

Examples to Understand Types of Initialization Parameters in Oracle:

Open the Linux virtual machine and open the terminal. Once you open the terminal.

Log in to the database as “Sqlplus / as sysdba”

You can see the screenshot shows connected to an idle instance. Let us start up the database. When we issue the command start up the oracle instance automatically searches for the spfile first. It searches for the file “spfilejaya.ora” because the instance name is jaya. The database is up and running now. Currently, we have logged in to the CDB$ROOT database.

We have discussed an example of the basic parameter DB_BLOCK_SIZE. Let’s see more about the parameter. Enter the command “show parameter block”

We have only entered the block. This shows all the parameters which contain the word block in it. We can see the parameter db_block_size. The type of the parameter is an integer and the Value of the parameter is 8192 which indicates the block size as 8KB. We will discuss the parameter in the database in further articles.

There is another way to know about the parameters present in the database. We can understand much better about these parameters using the oracle manual.

Note: The best way to learn more about the parameters using the database reference manual from oracle 12c,18c.

Step1: Open google chrome or any other browser and search for oracle database reference 12c or 18c or 19c.

Step2: If you are searching for the oracle 19c reference manual go to the below site. Please make sure you open the original oracle reference manual website. You can go through the below website as well to check more details about the parameters present in the reference manual of 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/index.html

Step3: You can see this is a very large manual we can download this manual as well. On the left end you can see the download option there you can download this manual to your desktop.

Step4: On top of the table contents, you can Initialization parameters If you click on that there will be an entire page describing initialization parameters.

You can see the uses of initialization parameters, basic initialization parameters, parameter files, and other information. Right-click on the basic initialization parameters and open a new tab. You can see the number of initialization parameters available below. There are 30 initialization parameters.

We will try to click on any other parameter for example I have selected DB_BLOCK_SIZE.

You can see the details about the parameter DB_BLOCK_SIZE. The Parameter type is a string and the Default value i.e. the block size of the parameter is 8192 i.e 8KB. Modifiable is No which means the parameter cannot be modified and this parameter cannot be modified in PDB as well. This is a Basic parameter that is displayed. You can read a lot of information about this DB_BLOCK_SIZE or any other parameter on the same page.

Step5: You can go and search for any other parameter as well. I have selected OS_ROLES. Even the expert DBA will be checking this manual in order to change any parameters in the database. According to the requirement they open the oracle manual and search for the required parameter and make the changes.

Step6: We have learned about the derived parameters. The SESSIONS parameter is an example of the SESSIONS parameter.

You can see the parameter type is an integer and the Default Value of this SESSIONS parameter is Derived (1.5 *PROCESSES) +22. This is a modifiable parameter that can be changed using the statement ALTER SYSTEM and this is modifiable in PDB as well. This is also a basic parameter.

Note: This is how we get a parameter from the manual and modify the database changes.

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