How to list parameter set at session level in Oracle

Often we set some parameters at the session level and run some commands. Now DBA may want to know what has been set in that session. In this post, we will check out How to list parameter set at the session level in Oracle

How to list parameter set at session level using oradebug

Using oradebug one can get a dump of session parameters that are modified at the session level, like optimization parameters.

Let’s take an example of this. We connect to one session

SQL> alter session set sql_trace=true;
Session altered.
SQL> alter session set hash_area_size=100M;
Session altered.
SQL> Run some thing

You can find the session parameter by connecting to another session and Using any of those to check the previous running session

SETOSPID Set OS pid of process to debug
SETORAPID ['force'] Set Oracle pid of process to debug

sqlplus / as sysdba
SQL> oradebug setospid 1233
Statement processed.
SQL> oradebug dump modified_parameters 1;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oracle/diagnostic_dest/diag/rdbms/TESTDB\testdb\trace\testdb_ora_1233.trc

Contents of the trace file:


Processing Oradebug command ‘dump modified_parameters 1'
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
hash_area_size = 100M
*** 2012-05-28 14:35:25.005
Oradebug command ‘dump modified_parameters 1' console output

How to list parameter set at session level using V$ view

It can also be found using the below query

select name, value
from V$SES_OPTIMIZER_ENV
where sid=&1
and name=’&parameter_name’;

In this case

select name, value
from V$SES_OPTIMIZER_ENV
where sid=512
and name='hash_area_size';

I hope you like this article on How to list parameter set at session level in Oracle

Related Articles
How to find optimizer underscore parameter
Difference between v$system_parameter and v$parameter