Assign Disk Quotas to Users

In Oracle 19c, managing disk quotas involves two distinct aspects:

1. Tablespace Quotas: This controls the amount of space a user can utilize within specific tablespaces.
2. Operating System Quotas: This enforces limits on the disk space a user can use on the underlying filesystem where data resides.

Tablespace Quotas:
Assign during user creation:

CREATE USER johndoe 
IDENTIFIED BY password 
QUOTA 10M ON USERS, 20M ON TEMP;

This grants John Doe a 10 MB quota on the USERS tablespace and a 20 MB quota on TEMP.
Assign after user creation:

ALTER USER johndoe QUOTA 15M ON USERS;

This changes John Doe's quota on the USERS tablespace to 15 MB.
Monitor usage:

SELECT username, tablespace_name, quota, bytes/1024/1024 used_mb
FROM user_ts_quotas;

Operating System Quotas:
This involves configuring quotas on the operating system level, impacting all users, not just within Oracle. Tools and options vary depending on your specific OS (Linux, Solaris, etc.). Here are general steps:

1. Check existing quotas: Use `quota` or `repquota` commands to understand current quotas.
2. Edit quota configuration: Edit files like `/etc/fstab` or use tools like `quotaon` and `setquota` based on your OS.
3. Apply quota to specific user/group: Set distinct quotas for specific users or groups.
4. Monitor usage: Use tools like `quota` or `du` to check user disk usage.

Important Notes:

• Tablespace quotas only control space within tablespaces, not the OS filesystem.
• Operating system quotas are independent of Oracle and affect all users, not just Oracle-specific usage.
• Carefully plan and test quota settings to avoid impacting legitimate user activities.

Remember, for specific instructions and best practices, consult your operating system documentation and follow security guidelines within your organization.

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:

CREATE USER
...
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.

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.
SQL>

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.
SQL>

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.

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