• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to list parameter set at session level in Oracle

How to list parameter set at session level in Oracle

March 14, 2023 by techgoeasy Leave a Comment

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

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to list parameter set at session level in Oracle
  • How to generate tkprof in EBS in 19c
  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us