In this post, we will discuss the Difference between v$system_parameter and v$parameter and other parameter view available in Oracle database.
Lets first start with the definition of each of them
V$PARAMETER
It displays the information about initialization parameters that are currently in effect for the session.
V$SYSTEM_PARAMETER
is the view which shows instance level parameters (and these are what all new sessions inherit)
V$SPPARAMETER
It displays the information about contents of the server parameter file. If a server parameter file was not used to start the instance, then ISSPECIFIED column contains FALSE value.
V$SYSTEM_PARAMETER2
It displays the information about initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view.
V$PARAMETER2
It displays information about the initialization parameters that are currently in effect for the session, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values displayed in the V$SYSTEM_PARAMETER2 view.
So many view to check, lets see the correct way to check the parameter values
How to check parameter values in Database
The right way to check instance parameter is the view V$SYSTEM_PARAMETER or V$SYSTEM_PARAMETER2 . V$PARAMETER may have difference values then instance level to session level settings
Lets see an example to explain it
SQL> select value from v$system_parameter where name = ‘session_cached_cursors’;
VALUE
——–
100
SQL> select value from v$parameter where name = ‘session_cached_cursors’;
VALUE
______
100
SQL> alter session set session_cached_cursors=200;
session altered
SQL> select value from v$system_parameter where name = ‘session_cached_cursors’;
VALUE
——-
100
SQL> select value from v$parameter where name = ‘session_cached_cursors’;
VALUE
———-
200
So we should rely on v$system_parameter for instance level parameter
Lets see example to demonstrate the difference between V$SYSTEM_PARAMETER and V$SYSTEM_PARAMETER2.
Actually these values are treated as separate parameter values (of the same parameter name) in V$PARAMETER2 and V$SYSTEM_PARAMETER2:
SQL> select value from v$system_parameter where name = ‘utl_file_dir’;
VALUE
——–
/tmp, *, /tmp
SQL>select value from v$system_parameters where name = ‘utl_file_dir’;
VALUE
——–
/tmp
*
/tmp
So this basically help us to see the multivalue parameter clearly
Now Lets see example to demonstrate difference between V$parameter and v$spparameter
Lets us assume a instance is started with spfile,then
SQL> select value from v$spparameter where name = ‘session_cached_cursors’;
VALUE
———-
100
SQL> select value from v$system_parameter where name = ‘session_cached_cursors’;
VALUE
——–
100
SQL> alter system set session_cached_cursors=200 scope=memory;
system altered
SQL> select value from v$spparameter where name = ‘session_cached_cursors’;
VALUE
——-
100
SQL> select value from v$system_parameter where name = ‘session_cached_cursors’;
VALUE
——–
200
SQL> alter system set session_cached_cursors=200 scope=spfile;
SQL> select value from v$spparameter where name = ‘session_cached_cursors’;
VALUE
——-
200
SQL> select value from v$system_parameter where name = ‘session_cached_cursors’;
VALUE
——
200
I hope you like this article on parameter values
how to check which query is generating more archives in oracle
oracle historical wait events
google translate URL in Oracle PLSQL
Oracle Flashback query
Supplemental logging in Oracle