Scope and duration of database options

You can set options at three levels of scope:

PUBLIC
user
temporary

Temporary options take precedence over user and PUBLIC settings. User level options take precedence over PUBLIC settings. If you set a user level option for the current user, the corresponding temporary option is set as well.

Some options (such as COMMIT behavior) are database-wide in scope. Setting these options requires specific privileges. Other options, such as the isolation_level option, can be applied to just the current connection and need no special privileges.

Changes to option settings take place at different times, depending on the option. Changing a global option such as the recovery_time option takes place the next time the database is started. Generally, only options that affect the current connection occur immediately. For example, you can change option settings in the middle of a transaction.

Caution

Do not change option values while a cursor is open. Changing the option values while a cursor is open can lead to inconsistent results within the cursor. For example, changing the date_format option while a cursor is open can result in some rows being returned in the old format and some rows returned in the new format. To ensure that the rows in the result set are computed consistently using the new option value, open the cursor after the option value is changed.

Setting PUBLIC options

Changing the value of an option for the PUBLIC role sets the permanent value of the option for all users who have not set their own value. An option value cannot be set for an individual user ID unless there is already a PUBLIC role setting for that option.

Some options that can only be set for the PUBLIC role take effect immediately for existing connections, even though the changed setting is not visible to users via the CONNECTION_PROPERTY function. An example of this behavior is the global_database_id option. For this reason, PUBLIC-only options should not be changed while other users are connected to the database.

Setting temporary options

Adding the TEMPORARY keyword to the SET OPTION statement changes the duration of the change. Ordinarily, changing the value of an option is permanent. When the SET TEMPORARY OPTION statement is executed, the new option value takes effect only for the current connection, and only for the duration of the connection. This new setting is held only in server memory and is not reflected in the SYSOPTION system view.

When the SET TEMPORARY OPTION statement is used to set a PUBLIC option, the value of the option is changed until the database is shut down, at which point the values of the temporary options for the PUBLIC role revert back to their permanent values.

Setting a temporary option for the PUBLIC role offers a security advantage. For example, when the login_mode option is enabled, the database relies on the login security of the system on which it is running. Enabling it as a temporary option setting means that a database relying on the security of a Windows domain is not compromised if the database is shut down and copied to a local computer. In this case, the login_mode option reverts to its permanent value, which could be Standard, a mode where integrated user authentication is not permitted.

Setting options for a SQL statement

The INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements have an OPTION clause that lets you specify how materialized views are used by the statement and how the query is optimized. This clause can also be used to specify an option setting that takes precedence over any PUBLIC or temporary option settings that are in effect, for that statement only. You can change the setting of the following options in the OPTION clause:

isolation_level
max_query_tasks
optimization_goal
optimization_level
optimization_workload
user_estimates