Query session parameter values of running session
I know this question is more than a year old, but in case anyone stumbles here like I did, you can query V$SES_OPTIMIZER_ENV:
SYS> select name, value
from V$SES_OPTIMIZER_ENV
where sid=54
and name='hash_area_size';
NAME VALUE
—————————————- ————————-
hash_area_size 100000
Source: http://oracleinaction.com/other-session-params/
Yes, there is a way to check this.
You can query V$PARAMETER with this SQL:
SELECT name, value
FROM v$parameter
WHERE name like 'commit_write'
;
NAME VALUE
-------------------- --------------------
commit_write (null)
Afterwards change the parameter on session basis:
ALTER SESSION SET commit_write = 'IMMEDIATE';
And query v$parameter again:
SELECT name, value
FROM v$parameter
WHERE name like 'commit_write'
;
NAME VALUE
-------------------- --------------------
commit_write IMMEDIATE