RelationalDBDesign RelationalDBDesign

Creating Users   «Prev  Next»
Lesson 6Disk Quotas
ObjectiveAssign disk quotas to your Users

Assign Disk Quotas to Users

For users to store objects in the default tablespace, you must assign each one a quota on that tablespace. A quota is an amount of disk space that can be used by a specific user in a specific tablespace. Users may have different quotas on different tablespaces in the database. The following MouseOver illustrates this.
When you create a user, you can use the QUOTA clause of the CREATE USER command to assign a quota on the default tablespace. The syntax is fairly simple. The following example shows how to assign 10 megabytes of quota on a tablespace named USER_DATA:

QUOTA 10M ON user_data

In addition to the default tablespace, you also may assign quotas on other tablespaces in the database. Users can have several quota assignments, up to one per tablespace. However, you do not need to assign quotas on the temporary tablespace. Oracle will use temporary tablespace as needed, regardless of whether or not a quota has been assigned.

Tablespaces Quotas - Quiz

Click the Quiz link below to answer a few questions about tablespaces and quotas.
Tablespaces Quotas - Quiz

Creating an Archive

You can create one or several Flashback Data Archives in existing tablespaces using the CREATE FLASHBACK ARCHIVE command; however, Oracle best practice recommends that you use dedicated tablespaces. All archives must have a default retention period using the RETENTION clause and can optionally be identified as the default archive using the DEFAULT keyword. The disk quota in an archive is limited by the disk space within the tablespace unless you assign a maximum amount of disk space in the archive using the QUOTA keyword.
In this example, you first create a dedicated tablespace for your Flashback Data Archive:
SQL> create tablespace fbda1
2 datafile '+data' size 10g;
Tablespace created.
Next, you create three Flashback Data Archives: one for the ES department with no quota limit and a ten-year retention period, a second one for the finance department with a 500MB limit and a seven-year retention period, and a third for all other users in the USERS4 tablespace as the default with a 250MB limit and a two-year retention period:
SQL> create flashback archive fb_es
2 tablespace fbda1 retention 10 year;
Flashback archive created.
SQL> create flashback archive fb_fi
2 tablespace fbda1 quota 500m
3 retention 7 year;
Flashback archive created.
SQL> create flashback archive default fb_dflt
2 tablespace users4 quota 250m
3 retention 2 year;
Flashback archive created.
You cannot specify more than one tablespace in the CREATE FLASHBACK ARCHIVE command; you must use the ALTER FLASHBACK ARCHIVE command to add a tablespace.