V$PARAMETER vs V$PARAMETER2 in Oracle

Back to: Oracle DBA Tutorials

V$PARAMETER vs V$PARAMETER2 in Oracle

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

V$PARAMETER vs V$PARAMETER2 in Oracle

In this article, we will learn about V$PARAMETER and the difference between V$PARAMETER and V$PARAMETER2.

Note: Open the Linux machine and start the database and open the pluggable database. After finishing the start-up of the database, open SQL developer and connect to the database as sys as sysdba. Once the connection is successful let us check the database using the command “show con_name”

We are in the CDB$ROOT database. Let us run a query to check the list of the both container and pluggable databases present.

Query: select con_id,name,open_mode from V$Containers;

If any of your pluggable databases are closed just use the command to open the pluggable databases ”Alter pluggable database all open;”

V$PARAMETER in Oracle:

Now, let us discuss the data dictionary V$PARAMETER. This view will show info about the parameters that are currently in effect for the session. Let us run a query with the data dictionary V$PARAMETER.

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

We can see the output from the query. We have all the parameters having con_id as 1 because we have logged in to the CDR$ROOT (jayadb) database. The data dictionary V$PARAMETER only displays ineffective with the current session. We do not find the other parameters in other pluggable databases.

We will run the same query with small changes as where clause with parameter as control files. We will get the information about control files.

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

From the output, we have a parameter with the name control_files and the value is we have two control files control01 and control02. We can see all the three columns’ISSES_MODIFIABLE, ISSYS_MODIFIABLE, and ISPDB_MODIFIABLE is FALSE. If we see FALSE in ISSES_MODIFIABLE then we cannot change the value in the session level. If we find FALSE in ISPDB_MODIFIABLE then we cannot change the value in the pluggable databases. But if ISSYS_MODIFIABLE is FALSE that does not mean we cannot change the value at the system level. When we see ISSYS_MODIFIABLE this means it is a static parameter. This means it could be changed but in a specific spfile only and need to restart the database using spfile.

• If ISSYS_MODIFIABLE=IMMEDIATE, changes can take effect immediately.
• If ISSYS_MODIFIABLE=DEFERRED, changes can take effect for future sessions.

We will have a lot of exercises about these values in future topics. We have another view called V$PARAMETER2. This parameter shows the exact same info as the V$PARAMETER that is currently in effect for the session but it is more readable in case if it has many lines compared to V$PARAMETER. Let us execute the same query but with the V$PARAMETER2 view.

Query: select name, value, isses_modifiable,issys_modifiable,ispdb_modifiable,con_id from V$PARAMETER2 where name=’control_files’;

From the output, you can see that the output is in two lines. We have control01 in the first line and control02 in the second line. From the outputs V$PARAMETER and V$PARAMETER2 we can clearly see that the V$PARAMETER has more readability compared to V$PARAMETER.

In this topic, we understood the difference between V$PARAMETER and V$PARAMETER2 and we understood about when the ISSYS_MODIFIABLE is false then the parameter can be changed from spfile, not in the memory.

In the next article, I am going to discuss V$SYSTEM_PARAMETER vs V$SYSTEM_PARAMETER2 in Oracle with Examples. Here, in this article, I try to explain V$PARAMETER vs V$PARAMETER2 in Oracle with Examples and I hope you enjoy this V$PARAMETER vs V$PARAMETER2 in Oracle article.