Controls the maximum amount of temporary file space a connection can use.

Allowed values

Integer [ k | m | g | p ]

Default

0

Scope

Can be set for a temporary option for the duration of the current connection or for the PUBLIC group. Takes effect immediately. DBA authority required.

Remarks

This option allows you to specify the maximum amount of temporary file space a connection can use before the request fails because it exceeds the temporary file space limit. The temp_space_limit_check option must be set to On (the default) for the max_temp_space option to take effect.

The default value 0 indicates that there is no fixed limit on the amount of temporary file space a connection can request. Any other value specifies the number of bytes of temporary file space a connection can use. You can use k, m, or g to specify units of kilobytes, megabytes, or gigabytes, respectively. If you use p, the argument is a percentage of the total amount of temporary file space available.

For connections that request temporary file space, the database server checks the limit against the setting of the max_temp_space option to make sure the request is under the maximum size. If the connection requests more temporary space than is allowed, the request fails and the error SQLSTATE_TEMP_SPACE_LIMIT is generated.

Can be set for an individual connection or for the PUBLIC group. Takes effect immediately. DBA authority required to set this option for the PUBLIC group.

See also

• temp_space_limit_check option [database]
• sa_disk_free_space system procedure

Example

Set a 1 GB limit for a connection:

SET OPTION PUBLIC.max_temp_space = '1g';

Both of the following statements set a 1 MB limit for a connection:

SET OPTION PUBLIC.max_temp_space = 1048576;

SET OPTION PUBLIC.max_temp_space = '1m';

Use five percent of the total temporary space available:

SET OPTION PUBLIC.max_temp_space = '5p';