Home » Oracle » Oracle Database » Difference between v$system_parameter and v$parameter

Difference between v$system_parameter and v$parameter

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’;

See also  How to install Oracle Database Cloud Backup Module

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top