Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Session Level parameters - Fetch

Dev_SQLNov 4 2019 — edited Nov 6 2019

Hi All,

I'm able to extract the System level parameters from v$parameter performance view.

I would like to Extract the Session level parameters as well, tried with the below, but I'm facing some limitation, kindly help.

select sys_context('USERENV', 'SESSIONID') from dual;

-- get the output as '40950517'

select * from v$session

where username='HR'

and audsid=40950517;

-- above query returns SID as 22

The below query doesn't show any output, but it has data for many other parameters.

select * from v$ses_optimizer_env

where sid=22

and lower(name)='plsql_code_type'

In the above HR session, '40950517' and SID '22', I have run the below query and want to check what is there at present.

ALTER SESSION SET plsql_code_type = 'NATIVE';

I can get the above setting information from 'user_plsql_object_settings' view, this is fine. The above scenario is just an example, basically, I'm interested to fetch the session level parameters value, how do we get this? Kindly help, thank you.

This post has been answered by L. Fernigrini on Nov 5 2019
Jump to Answer
Comments
Post Details
Added on Nov 4 2019
4 comments
5,279 views