Thursday, January 10, 2019

ALTER SESSION RESET parameter works but doesn't work

Always check out the original article at 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:

The statement executed without a hitch in Oracle Database 12c Enterprise Edition Release - 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.

1 comment:

Anonymous said...

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

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles