The USABLE_SPACES Session Environment Setting

Users who hold the Resource privilege can create databases or database objects in any dbspace or dbslice of XPS 8.50 and earlier releases. This effectively restricts the ability of the DBA or of the DBSA to manage storage resources, and can degrade system performance when individual users have consumed storage space that is needed by other critical processes.

Version 8.51 of Extended Parallel Server introduces a new session environment variable, called USABLE_SPACES, that enables the DBA to specify a list of dbspaces and dbslices that are available to store database objects that users create in the following DDL operations:

• ALTER FRAGMENT ... ADD
• CREATE DUPLICATE TABLE
• CREATE TABLE
• CREATE Temporary TABLE
• CREATE DATABASE
• CREATE INDEX
• SELECT ... INTO [ Table Clauses ]

Here CREATE TABLE includes OPERATIONAL, RAW, STANDARD, and STATIC tables, and CREATE Temporary TABLE includes SCRATCH and TEMP tables.

See the Guide to SQL: Syntax for more information about these SQL statements, each of which can specify a storage location for the new database object or new database that it creates.

If a user who does not hold the DBA privilege attempts to execute a DDL statement that stores a database object outside the storage spaces that are listed in the USABLE_SPACES specification for the current session, the DDL statement fails with an error.