Thursday, January 10, 2019

ALTER SESSION RESET parameter works but doesn't work

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I was wondering why it's not possible to reset a session modifiable parameter to its default value (that is the system wide current value), then just out of curiosity I tried to execute the following:

ALTER SESSION RESET SPATIAL_VECTOR_ACCELERATION;
 
The statement executed without a hitch in Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.

Unfortunately it didn't reset anything to its original value.

Note that this syntax is not supported in the syntax diagram, so it works but it doesn't do what (is not) advertised.

May be in a future release?

By the way, the only workaround I could think of in order to save the initial value of a session parameter in a session-permanent place is to store it into a named context parameter (provided you don't clear the context at any time somewhere else...).
Storing it into a packaged variable can lead to false results in case the package state is reset after altering the value of the session parameter.


If anybody has better ideas, I'll be glad to know.
at

1 comment:

  1. AnonymousTuesday, 29 January, 2019

    what about table for values (or statements) and package as api for changing, applying, creating etc ?

    ReplyDelete

I appreciate your comment however bear in mind that I might not have the time to reply soon.
Normally I do not reply to all comments but I am glad if you found something useful or if you learned something new, in that case I strongly encourage you to promote the article with the +1 google button.
Flavio