8

I don't think there's a way to do this, but I'd like to be able to query what value a running session currently has for its COMMIT_WRITE session parameter.

I haven't seen anything in the v$ performance views... anyone know of a SYS x$ view from which such a value could be retrieved?


Clarification - the need is to pull the parameter setting for another session, running independent of current session.

6

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/

4

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
  • Adding clarification to original question - the challenge is to find out what this v$parameter value is for a different session. – KevinKirkpatrick Jan 9 '15 at 17:24
  • You probably can use oradebug command to dump modified parameter values. I don't think non-optimizer related parameters from another session are visible from any gv$ or x$. Sometimes they are stored in session UGA and not exposed. Curious minds want to know, why do you need this? – Raj Jan 9 '15 at 18:19
  • Wanted to sanity-check that a logon trigger properly set the commit_write parameter for that session (and that the commit_write parameter doesn't change for that session even if I do an ALTER SYSTEM change)... then occured to me "hey, if there's a way to 'peak' at the parameters of running sessions, that'd be kind of handy". Not critical - we'll find out soon enough if it didn't work (it seems to have), but wanted to see if anyone had quick way to verify directly. – KevinKirkpatrick Jan 10 '15 at 8:12
  • 1
    I don't think that it's possible to get the value of a different session. But normally you don't change that value twice a day so the the other sessions should have the configured system value. If you want to know if the value gets changed on session basis you have to trace that session. A logon trigger won't help much because right after logon the session based value is still the same as the system wide value. – ora-600 Jan 12 '15 at 9:31
  • "and that the commit_write parameter doesn't change for that session even if I do an ALTER SYSTEM change)". This is impossible to enforce, unless of course you revoke 'ALTER SESSION' or 'ALTER SYSTEM' from users who you don't want to make changes. I hope ALTER SYSTEM is not granted to non DBA users. Revoking ALTER SESSION is probably easier, set commit_write at system level but you have to account for vendor code requirements if that is the case. Also ALTER SESSION will override ALTER SYSTEM setting at session level if it is done after ALTER SYSTEM. So, revoke that privilege to be safe. – Raj Jan 12 '15 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.