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

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’;
/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 find session generating lots of redo

How to find the waitevent History of the Oracle session

How to use google translate URL in Oracle plsql

What you need to know about Oracle Flashback query

 

Leave a Reply