|
What are Tablespace Quota and default tablespace?Tablespace Quota:
- A tablespace is a logical storage unit within an Oracle database because it is not visible in the file system of the machine, the database resides on.
- The Tablespace quotas are used to assign users with limited size on tablespaces.
- Each user can be assigned a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota does two things:
1. Users with privileges to create certain types of objects can create those objects in the specified tablespace.
2. Oracle limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.
-By default, a user has no quota on any tablespace in the database.
- If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects.
Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.
- A user can either be assigned individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces.
- Specific quotas prevent a user's objects from consuming too much space in the database.
- A user's tablespace quotas can be assigned when the user is created, or add or change quotas later. If a new quota is less than the old one, then the following conditions hold true:
- If a user has already exceeded a new tablespace quota, the user's objects in the tablespace cannot be allocated more space until the combined space of these objects falls below the new quota.
- If a user has not exceeded a new tablespace quota, or if the space used by the user's objects in the tablespace falls under a new tablespace quota, the user's objects can be allocated space up to the new quota.
Default tablespace:
- Default tablespaces are used for users who are not configured with default tablespaces.
- Each user should have a default tablespace. When a user creates a schema object and specifies no tablespace to contain it, Oracle stores the object in the user's default tablespace.
- The default setting for every user's default tablespace is the SYSTEM tablespace.
- If a user does not create objects, and has no privileges to do so, this default setting is fine.
- However, if a user creates any type of object, you should specifically assign the user a default tablespace.
- Using a tablespace other than SYSTEM reduces contention between data dictionary objects and user objects for the same datafiles.
- In general, it is not advisable for user data to be stored in the SYSTEM tablespace.
- A user's default tablespace can be set during user creation, and change it later with the ALTER USER statement.
- Changing the user's default tablespace affects only objects created after the setting is changed.
- When you specify the user's default tablespace, also specify a quota on that tablespace.
|
