ALTER SYSTEM

Purpose

Use this statement to configure System-wide parameters.

Prerequisite

You need the ALTER SYSTEM system privilege.

Syntax

alter_system::=

Alter system statement

Usage Notes

  • The session-based parameters are initialized with the system-wide parameters ALTER SYSTEM. However, you can overwrite it with the ALTER SESSION statement. The current settings are available in the EXA_PARAMETERS system table.
  • If a value is changed through ALTER SYSTEM, it will only impact new connections to the database.
  • The following table describes the parameters you can set:
    • Parameters Description
    CONSTRAINT_STATE_DEFAULT

    This parameter defines the default state (ENABLE or DISABLE) of a new constraint when the new constraint's state is not specified during creation. Changing it does not alter any existing constraints. For more information, see CREATE TABLE and ALTER TABLE (Constraints).
    DEFAULT_CONSUMER_GROUP

    Specifies the default consumer groups for users and roles. For more details about consumer groups, see Resource Manager.
    DEFAULT_LIKE_ESCAPE_CHARACTER

    Defines the escape character for the LIKE predicate (for more details, see Predicates) if it is not specified explicitly.
    HASHTYPE_FORMAT

    Defines the output format of data stored as HASHTYPE. The following values can be set:

    • HEX: Simple ouput as hex string. For example: 550e8400e29b11d4a716446655440000
    • UUID: UUID style output. For example: 550e8400-e29b-11d4-a716-446655440000
    • BASE64: base64-encoded ascii string. For example: chatkYY/Tsai+DDVIukNnw
    • BASE64URL: base64url-encoded ascii string. For example: chatkYY_Tsai-DDVIukNnw

    The default value is HEX.

    UUID only works for columns with data type HASHTYPE(16 byte). If the column size is set to a value other than 16 byte, the output will be in HEX even if you set it to UUID.
    IDLE_TIMEOUT

    Specifies a timeout in seconds. The session is killed after the specified time if there is no activity. To disable the limit, set the attribute to 0 (zero).

    The default value is 86400 seconds (24 hours).
    NLS_DATE_FORMAT

    Sets the date format used for conversions between dates and strings.

    For information about date/time formats, see Date/Time Format Models.

    NLS_DATE_LANGUAGE

    Sets the language to use when full or abbreviated format for day and month. Possible languages are English (default) and German. English can be set using ENG or ENGLISH and German with DEU, DEUTSCH, or GERMAN.

    For information about date/time formats, see Date/Time Format Models.

    NLS_FIRST_DAY_OF_WEEK

    Defines the first day of a week as an integer 1-7 for Monday – Sunday.
    NLS_NUMERIC_CHARACTERS

    Defines the decimal and group characters used for representing numbers. This parameter is also relevant to the use of numeric format models.

    For more information, see Numeric Format Models.
    NLS_TIMESTAMP_FORMAT

    Sets the timestamp format used for conversions between timestamps and strings. For information on the possible formats, refer to the Date/Time Format Models section.

    PASSWORD_EXPIRY_POLICY

    Specifies the expiry rules for user passwords. These system values can be overwritten for single users through ALTER USER statement.

    For more information, see Database Users and Roles and Privileges.

    PASSWORD_SECURITY_POLICY

    Specifies the security rules for user passwords. For details for all the possible parameters, see Database Users and Roles and Privileges.
    PROFILE

    Enables or disables profiling for new connections by setting the values ON or OFF. For more details, see Profiling.

    Existing connections are not affected, only connections that are created after the ALTER command.

    QUERY_CACHE

    The parameter QUERY_CACHE defines the usage of a read cache for SELECT queries. If the syntactically identical query is sent multiple times (except upper/lowercase, spaces, ...), then the database can read the result directly out of a cache instead of executing the query. This is only applicable if the corresponding schema objects haven't changed in the meantime.

    The following values can be set:

    • ON: The query cache is used. Each query result that is expensive enough (defined internally by resource usage) is read from and written into the cache.
    • OFF: The query cache is not used.
    • READONLY: Results are read from the cache, however, additional new queries will not be cached.

    Whether a query was returned from the cache can be determined by the column EXECUTION_MODE in the corresponding system tables (EXA_SQL_LAST_DAY).
    QUERY_TIMEOUT

    Defines how many seconds a statement may run before it is automatically aborted. When this point is reached, the statement may finish with an exception within a few seconds through an internal cancellation point. If this fails (for example because there are no such cancellation points or the query is slowed down due to disk operations) the query is terminated forcefully and the transaction is rolled back (including an internal reconnect).

    Time spent waiting for other transactions (in state Waiting for session) is included. In case of EXECUTE SCRIPT the QUERY_TIMEOUT is applied to the script as a whole, and when reaching the timeout the script is terminated (including any statements being executed by the script).

    Any changes of the QUERY_TIMEOUT within a script will only be applied when the script exits. The default value for QUERY_TIMEOUT is '0' (no restrictions).
    REPLICATION_BORDER Specifies the upper size limit for a "small table" that the query optimizer will consider replicating. The default replication border is 100,000 rows and the limit is 10 billion rows. For more information, see Best Practices.
    SCRIPT_LANGUAGES

    Defines the script language aliases.

    SCRIPT_OUTPUT_ADDRESS

    		Defines the hostname (or IP address) and the port number of a server to which the standard output (for example, by Python's print function) of UDF scripts are forwarded. You can deactivate the redirection of the script output by setting this parameter to the empty string.
    SESSION_TEMP_DB_RAM_LIMIT

    Sets the limit for tempDBRAM usage by each session. If the parameter is set to OFF, no limit is applied. Users can alter this parameter for an individual session. The strictest limit will apply.

    The parameter must be specified as an unsigned integer, optionally followed by a single-character unit symbol M (megabytes), G (gigabytes), or T (terabytes). There must be no space between the value and the unit symbol. For example: 10G is valid, but not 10GB or 10 G.

    If a unit is not specified, the value is interpreted as megabytes.

    Since TEMP_DB_RAM can be swapped to disk, SESSION_TEMP_DB_RAM_LIMIT can be set to a larger value than the DB_RAM size.

    SNAPSHOT_MODE

    Defines the transaction Snapshot Mode for connections. The possible values are:

    • OFF: Snapshot mode is off and queries selecting from system tables or user tables will take read locks on those tables.
    • SYSTEM TABLE: Snapshot mode is on and queries selecting from system tables will not take read locks on them. However, queries selecting from user tables will continue to get read locks on them.

    For more information, see Snapshot Mode.
    SQL_PREPROCESSOR_SCRIPT

    Defines a preprocessor script. If the script is specified (a regular script which was created through CREATE SCRIPT), then every executed SQL statement is preprocessed by that script. For more information on SQL preprocessing, see SQL Preprocessor. For details about the script language, see Scripting.

    Appropriate user privileges must exist for executing this script. You can deactivate the preprocessing by specifying the empty string '' or NULL.
    ST_MAX_DECIMAL_DIGITS

    Defines the maximum significant digits used when casting from geometry data type to a string. You can use it to convert from geometry data type to string. It does not have any influence on the internal storage of geometry type, and on the geometry operations. The default value is 16, and the allowed range is <1,16>. For additional information, refer to the Type Conversion Rules section.

    TEMP_DB_RAM_LIMIT

    Sets the limit for total tempDBRAM usage by all sessions. If the parameter is set to OFF, no limit is applied.

    The parameter must be specified as an unsigned integer, optionally followed by a single-character unit symbol M (megabytes), G (gigabytes), or T (terabytes). There must be no space between the value and the unit symbol. For example: 10G is valid, but not 10GB or 10 G.

    If a unit is not specified, the value is interpreted as megabytes.

    Since TEMP_DB_RAM can be swapped to disk, TEMP_DB_RAM_LIMIT can be set to a larger value than the DB_RAM size.

    TIME_ZONE

    Defines the time zone in which the values of type TIMESTAMP WITH LOCAL TIME ZONE are interpreted. For more information, refer to the Date/Time Data Types section. The list of supported timezones is available in the system table EXA_TIME_ZONES. The function SESSIONTIMEZONE returns the current session time zone.
    TIME_ZONE_BEHAVIOR

    Defines the course of action for ambiguous and invalid timestamps within a certain time zone. For more information, refer to the Date/Time Data Types section.

    TIMESTAMP_ARITHMETIC_BEHAVIOR

    Defines the behavior for + and - operators.

    • INTERVAL: The difference of two datetime values is an interval. When adding a decimal value to a timestamp, the number is rounded to an integer and a full day is added.
    • DOUBLE: The difference of two datetime values is a double. When adding a decimal value to a timestamp, the fraction of days is added (hours, minutes, ...).
    USER_TEMP_DB_RAM_LIMIT

    Sets the limit for total tempDBRAM usage by all sessions of a user. If the parameter is set to OFF, no limit is applied.

    The parameter must be specified as an unsigned integer, optionally followed by a single-character unit symbol M (megabytes), G (gigabytes), or T (terabytes). There must be no space between the value and the unit symbol. For example: 10G is valid, but not 10GB or 10 G.

    If a unit is not specified, the value is interpreted as megabytes.

    Since TEMP_DB_RAM can be swapped to disk, USER_TEMP_DB_RAM_LIMIT can be set to a larger value than the DB_RAM size.

Examples

ALTER SYSTEM SET TIME_ZONE='EUROPE/BERLIN';
ALTER SYSTEM SET TIME_ZONE_BEHAVIOR='INVALID SHIFT AMBIGUOUS ST';
ALTER SYSTEM SET NLS_DATE_FORMAT='DAY-DD-MM-MONTH-YYYY';
ALTER SYSTEM SET NLS_DATE_LANGUAGE='DEU';
ALTER SYSTEM SET NLS_FIRST_DAY_OF_WEEK=1;
ALTER SYSTEM SET NLS_NUMERIC_CHARACTERS=',.';
ALTER SYSTEM SET QUERY_TIMEOUT=120;
ALTER SYSTEM SET CONSTRAINT_STATE_DEFAULT='DISABLE';
ALTER SYSTEM SET SQL_PREPROCESSOR_SCRIPT=my_schema.my_script;
ALTER SYSTEM SET PASSWORD_SECURITY_POLICY='MIN_LENGTH=8:MIN_NUMERIC_CHARS=1';
ALTER SYSTEM SET PASSWORD_EXPIRY_POLICY='EXPIRY_DAYS=180:GRACE_DAYS=7';
ALTER SYSTEM SET SCRIPT_OUTPUT_ADDRESS='10.242.2.21:3000'
ALTER SYSTEM SET TEMP_DB_RAM_LIMIT = 1024;
ALTER SYSTEM SET TEMP_DB_RAM_LIMIT = '400G';
ALTER SYSTEM SET USER_TEMP_DB_RAM_LIMIT = '50G';
ALTER SYSTEM SET SESSION_TEMP_DB_RAM_LIMIT = 'OFF';
ALTER SYSTEM SET SNAPSHOT_MODE = 'OFF';
--or
ALTER SYSTEM SET SNAPSHOT_MODE = 'SYSTEM TABLES';
ALTER SYSTEM SET IDLE_TIMEOUT = 3600;
ALTER SYSTEM SET REPLICATION_BORDER = 1000000;