You can set options at three levels of scope: public, user, and 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 DBA permissions. Other options, such as ISOLATION_LEVEL, can also be applied to only the current connection, and need no special permissions.
Changes to option settings take place at different times, depending on the option. Changing a global option such as RECOVERY_TIME takes place the next time the server is started. This list contains some of the options that take effect after the server is restarted:
Database options that require restarting the server: |
|---|
CACHE_PARTITIONS |
CHECKPOINT_TIME |
OS_FILE_CACHE_BUFFERING |
PREFETCH_BUFFER_LIMIT |
PREFETCH_BUFFER_PERCENT |
RECOVERY_TIME |
Options that affect only the current connection generally take place immediately. You can change option settings in the middle of a transaction, for example.
WARNING! Changing options when a cursor is open can lead to unreliable results. For example, changing DATE_FORMAT might not change the format for the next row when a cursor is opened. Depending on the way the cursor is being retrieved, it might take several rows before the change works its way to the user.
