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 check parameter at the session level using the 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 the session level in Oracle

Related Articles
How to find optimizer underscore parameter
Difference between v$system_parameter and v$parameter: Check out this post for the Difference between v$system_parameter and v$parameter and other parameter views available in the Oracle database.
Optimizer statistics preferences in Oracle: check out what is Optimizer statistics preferences in Oracle, various preferences available in Oracle 19c, How to set the Optimizer statistics preferences
ASM  Initialization Parameters : ASM  Initialization Parameters like ASM_DISKSTRING and ASM_DISKGROUP decide about the disk to scan and ASM diskgroup to be mounted during startup
10053 trace in Oracle: Visit this page for details on 10053 trace in Oracle ,how to enable and disable it