You can set options at 3 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 just 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. The following 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 |
DISK_STRIPING |
MAIN_CACHE_MEMORY_MB |
MAIN_RESERVED_DBSPACE_MB |
OS_FILE_CACHE_BUFFERING |
OUT_OF_DISK_MESSAGE_REPEAT |
OUT_OF_DISK_WAIT_TIME |
PREFETCH_BUFFER_LIMIT |
PREFETCH_BUFFER_PERCENT |
RECOVERY_TIME |
TEMP_CACHE_MEMORY_MB |
TEMP_RESERVED_DBSPACE_MB |
Options that affect only the current connection generally take place immediately. You can change option settings in the middle of a transaction, for example. One exception to this is that changing options when a cursor is open can lead to unreliable results. For example, changing DATE_FORMAT may not change the format for the next row when a cursor is opened. Depending on the way the cursor is being retrieved, it may take several rows before the change works its way to the user.
Adding the TEMPORARY keyword to the SET OPTION statement changes the duration of the change. Ordinarily an option change is permanent: it will not change until it is explicitly changed using the Set Option statement.
When the SET TEMPORARY OPTION statement is executed, the new option value takes effect only for the current connection, and for the duration of the connection.
When the SET TEMPORARY OPTION is used to set a PUBLIC option, the change is in place for as long as the database is running. When the database is shut down, Temporary options for the PUBLIC user ID revert back to their permanent value.
Setting an option for the PUBLIC user ID temporarily 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 temporarily means that a database relying on the security of a Windows domain will not be compromised if the database is shut down and copied to a local machine. In this case, the LOGIN_MODE option will revert to its permanent value, which could be Standard, a mode where integrated logins are not permitted.
