Why V$PARAMETER And V$SPPARAMETER Gives Different Parameter Values?

We may sometimes face a situation where we get two different values for the same initialization  parameter from the views v$PARAMETER and V$SPPARAMETER.

Lets see an example here:

The parameter processes gives two different values 150 and 200 when queried from v$PARAMETER and V$SPPARAMETER respectively.

SQL> show parameter processes

NAME                    TYPE        VALUE
———————– ——————————
processes              integer     150

SQL> show parameter spfile;

NAME                    TYPE        VALUE
———————————— ———– ——————————
spfile                  string      C:\ORACLE\PRODUCT\10.2.0\DB_2\spfile.ora

Here the database runs in spfile.

Now change the value of processes parameter to 200 and check the values from the views.

SQL> alter system set processes=200 scope=spfile;

System altered.

SQL> show parameter processes

NAME                                 TYPE        VALUE
———————————— ———– ——————————
processes                            integer  150

SQL> select value from V$parameter where name=’processes’;

VALUE
————————-

150

SQL> select value from V$spparameter where name=’processes’;

VALUE
————————-

200

See, for the same parameter two different views gives two different values!!

Now after bouncing the database.

SQL> shutdown immediate;
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.

SQL> select value from V$parameter where name=’processes’;

VALUE
————————
200

SQL> select value from V$spparameter where name=’processes’;

VALUE
————————

200

Hope the confusion is sorted out now!!!

V$spparamter displays those values which we are specified in the spfile, whether or not applicable in the session.

But V$paramter displays only values which are applicable for the session. V$paramter has nothing to do with the pfile/spfile it used to start up; it just displays which values those are in effect for the session.

Research and Article Contribution : Divya