Oracle7 Server Concepts
User Tablespace Settings and Quotas
As part of every user's security domain, the database administrator can set several options regarding tablespace usage:
- the user's default tablespace
- the user's temporary tablespace
- space usage quotas on tablespaces of the database for the user
Default Tablespace
When a user creates a schema object and specifies no tablespace to contain the object, the object is placed in the user's default tablespace. This enables Oracle to control space usage in situations where an object's tablespace is not specified. You set a user's default tablespace when the user is created; you can change it after the user has been created.
Temporary Tablespace
When a user executes a SQL statement that requires the creation of a temporary segment, Oracle allocates that segment in the user's temporary tablespace.
Tablespace Access and Quotas
Each user can be assigned a tablespace quota for any tablespace of the database. Two things are accomplished by assigning a user a tablespace quota:
- The user can use the specified tablespace to create objects, provided that the user has the appropriate privileges.
- The amount of space that can be allocated for storage of a user's objects within the specified tablespace can be limited.
By default, each user has no quota on any tablespace in the database. Therefore, if the user has the privilege to create some type of schema object, he must also have been either assigned a tablespace quota in which to create the object or been given the privilege to create that object in the schema of another user who was assigned a sufficient tablespace quota.
You can assign two types of tablespace quotas to a user: a quota for a specific amount of disk space in the tablespace, specified in bytes, Kb, or Mb, or a quota for an unlimited amount of disk space in the tablespace. You should assign specific quotas to prevent a user's objects from consuming too much space in a tablespace.
Tablespace quotas are not considered during temporary segment creation:
- Temporary segments can be created in a tablespace for which a user does not have a quota.
You can assign a tablespace quota to a user when you create that user, and you can change that quota or add a different quota later.
Revoking Tablespace Access
Revoke a user's tablespace access by altering the user's current quota to zero. With a quota of zero, the user's objects in the revoked tablespace remain, yet the objects cannot be allocated any new space.