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=’¶meter_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
Leave a Reply