V$SYSTEM_PARAMETER vs V$SYSTEM_PARAMETER2 in Oracle

Back to: Oracle DBA Tutorials

V$SYSTEM_PARAMETER vs V$SYSTEM_PARAMETER2 in Oracle

In this article, I am going to discuss V$SYSTEM_PARAMETER vs V$SYSTEM_PARAMETER2 in Oracle with Examples. Please read our previous article where we discussed V$PARAMETER vs V$PARAMETER2 in Oracle with Examples.

V$SYSTEM_PARAMETER vs V$SYSTEM_PARAMETER2 in Oracle

In this article, we will understand the view V$SYSTEM_PARAMETER. This view shows instance-level parameters. These are the new sessions inherited. We will run a query with the view v$SYSTEM_PARAMETER.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, ISDEFAULT, con_id from V$SYSTEM_PARAMETER order by name;

This is the output obtained from the query. We can even check the number of parameters by counting the rows.

We have a total count of 475 rows. Most of the parameters we see have con_id as 0 which is the bucket and this is at the instance level. To understand this more we will check it on the virtual machine. Open the Linux machine and open the file manager.

Once you open click on Computer and go to the below location.

Once you are in the path you can see the parameter files.

You can see the spfile as spfilejayadb.ora and pfile as init.ora. The spfile spfilejayadb.ora, we cannot edit this file or change this file manually but we can open this file. Let’s try to open this file.

We can so many redlines which indicate that this file is protected and cannot be changed manually. In the above image, if we have a look at it, we can see there are 20 to 30 parameters around. But from the query we have seen, there are more than 400 parameters. Let us try to understand the concept of having 30 parameters in the spfile. Let us take a look at the query output again.

We have a column called ISDEFAULT column. The value of the ISDEFAULT column is TRUE. This indicates the default value coming from the instance. If the ISDEFAULT value is FALSE then the value is from the spfile or pfile. Let us scroll down a bit and check for the FALSE value.

We can see the parameter audit_file_dest parameter has the ISDEFAULT value as FALSE. The value FALSE is the parameter is coming from spfile or pfile. Let us check whether the parameter is present in the spfile or not.

We can see the parameter is present in the spfilejayadb.ora. So, the conclusion is if the ISDEFAULT value is TRUE then the parameter is from the instance and if the ISDEFAULT value is FALSE then the parameter is from the spfile or pfile. Now let us filter only ISDEFAULT value as FALSE from the query.

Query: select name, value, isses_modifiable, issys_modifiable ,ispdb_modifiable, ISDEFAULT, con_id from V$SYSTEM_PARAMETER where ISDEFAULT=’FALSE’ and con_id = 0 order by name;

Let us check the total number of parameters using count rows.

You can see 18 rows only. All these 18 rows of parameters exist in the spfile. Previously we have queried using V$PARAMETER. Let’s compare the two parameters V$PARAMETER and V$SYSTEM_PARAMETER using two simple queries with nls_date_format.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, ISDEFAULT, con_id from V$SYSTEM_PARAMETER where name=’nls_date_format’.

From the output, you can see the value is null as it is a derived parameter and ISSES_MODIFIABLE is True as it can be modified at the session level. Now let us change the date format to “dd-mm-yyyy”.

Command: alter session set nls_date_format=’dd-mm-yyyy’;

So, we have changed the date format. Let’s run the same query again and check if the null is changed or not.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, ISDEFAULT, con_id from V$SYSTEM_PARAMETER where name=’nls_date_format’.

We can still see that the value of the parameter is null because it is a derived parameter. The changes affect only the current session. So, any new session connecting to this database will read only the null value, not the value that we have modified. The modified value is limited to the only current session. Let’s check that using a query with the V$PARAMETER view.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, con_id from V$PARAMETER where name=’nls_date_format’.

So, the changes are affected only current session. We understood that V$SYSTEM_PARAMETER is a system-level view. There is another view called V$SYSTEM_PARAMETER2. This is more readable compared to V$SYSTEM_PARAMETER. This is the view that shows instance-level parameters. Let’s check that with a query.

Query: select name,value,isses_modifiable,issys_modifiable,ispdb_modifiable from v$SYSTEM_PARAMETER2 where name=’control_files’;

So, we can see the output is more readable compared to V$SYSTEM_PARAMETER. In this article, we have learned that V$SYSTEM_PARAMETER is an instance-level view. If any changes were done at the system level will not be displayed by V$SYSTEM_PARAMETER.

In the next article, I am going to discuss Search Order for a Parameter file in Oracle with Examples. Here, in this article, I try to explain V$SYSTEM_PARAMETER vs V$SYSTEM_PARAMETER2 in Oracle with Examples and I hope you enjoy this V$SYSTEM_PARAMETER vs V$SYSTEM_PARAMETER2 in Oracle article.