Checks for catalog store temporary space on a per connection basis.
Option can be set at the database (PUBLIC) level only.
Requires the SET ANY SYSTEM OPTION system privilege to set this option. Takes effect immediately.
When TEMP_SPACE_LIMIT_CHECK is ON, the database server checks the amount of catalog store temporary file space that a connection uses. If a connection requests more than its quota of temporary file space when this option is set to OFF, a fatal error can occur. When this option is set to ON, if a connection requests more than its quota of temporary file space, the request fails and the error “Temporary space limit exceeded” is returned.
Two factors are used to determine the temporary file quota for a connection: the maximum size of the temporary file, and the number of active database connections. The maximum size of the temporary file is the sum of the current size of the file and the amount of disk space available on the partition containing the file. When limit checking is turned on, the server checks a connection for exceeding its quota when the temporary file has grown to 80% or more of its maximum size, and the connection requests more temporary file space. Once this happens, any connection fails that uses more than the maximum temporary file space divided by the number of active connections.
You can obtain information about the space available for the temporary file using the sa_disk_free_space system procedure.
A database is started with the temporary file on a drive with 100MB free and no other active files on the same drive. The available temporary file space is 100MB. The DBA enters:
SET OPTION PUBLIC.TEMP_SPACE_LIMIT_CHECK = 'ON'
As long as the temporary file stays below 80MB, the server behaves as it did before. Once the file reaches 80MB, the new behavior might occur. Assume that with 10 queries running, the temporary file needs to grow. When the server finds that one query is using more than 8MB of temporary file space, that query fails.