V$SPPARAMETER in Oracle

Back to: Oracle DBA Tutorials

V$SPPARAMETER in Oracle with Examples

In this article, I am going to discuss V$SPPARAMETER in Oracle with Examples. Please read our previous article where we discussed How to Enable DDL Logging in Oracle with Examples.

V$SPPARAMETER in Oracle

We have already learned about the data dictionaries named V$PARAMETER, V$PARAMETER2, V$SYSTEM_PARAMETER, and v$SYSTEM_PARAMETER2. Now, we will try to learn about V$SPPARAMETER. Let us have a good understanding of this parameter. So, go to the below location and open the spfile.

Open the spfile.

We already knew that this spfile cannot be edited and the value of the parameter open_cursors is 410. This is the value that we have changed recently. Now, let’s do a small exercise and understand better about V$SPPARAMETER. Connect to the database as “Sqlplus / as sysdba”.

Now, that we are connected to the database let’s shut down the database and start the database using the spfile.

We have registered the listeners as well so we need to wait for listeners to register to the database. Let’s go ahead and open the SQL developer and connect to the database as sysdba.

So, that we have connected to the database let’s check the con_name.

Now, that we are connected to the root database. Let’s go ahead and run the query to check the value of the parameter open_cursors that we have selected.

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

We can see the value of open_cursors is 410. The value is the same as the value in spfile. We can see the ISDEFAULT is FALSE. The FALSE indicates the value is coming from spfile or pfile. Now, let’s change the value of open_cursors to 310 with scope in memory.

Command: alter system set open_cursors =310 container =all scope =memory;

Now, let’s run the same query again to check the value of the parameter open_cursors.

But these changes didn’t write to the spfile. If we go ahead and check the value of open_cursors in spfile we will still find the value as 410. This is where Oracle introduced a parameter called V$SPPARAMETER. This view displays the contents of the spfile. If the spfile was not used to start the instance then each row of the view will contain FALSE in the ISPECFIIED column. So, let’s run a query to check with V$SPPARAMETER.

Query: select * from V$SPPARAMETER where name =’open_cursors’;

We can see the value of open_cursors is still 410 in spfile. If we do not have access to spfile but have to check the parameters present in the spfile we can use the view V$SPPARAMETER. This is purpose of V$SPPARAMETER. We can use the V$SPPARAMETER to check the parameters present in the spfile.

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