| Lesson 6 | Disk Quotas |
| Objective | Assign disk quotas to your Users |
Assign Disk Quotas to Users
I am working as an Oracle DBA .
How do I assign disk quotas to my users using Oracle 12c.
In Oracle Database 12c, managing disk quotas for users is an essential task that controls the amount of disk space allocated to each user on different tablespaces. As a Database Administrator (DBA), follow the steps below to meticulously assign and manage disk quotas to ensure optimized and secure utilization of database resources.
Steps to Assign Disk Quotas to Users in Oracle 12c:
- Evaluate User Requirements:
Prior to allocating disk quotas, perform a thorough analysis of each user’s requirements. Assess the volume and type of data that each user will be handling to make informed allocation decisions.
- SQL Command for Allocating Quotas: Utilize the `ALTER USER` SQL command to assign disk quotas. Specify the exact amount of space and the tablespace on which the quota is allocated.
- Syntax for Assigning Quotas: The following SQL command illustrates how to assign a quota to a user:
ALTER USER username
QUOTA size_in_MB ON tablespace_name;
Replace `username` with the actual user’s name, `size_in_MB` with the amount of space, and `tablespace_name` with the name of the tablespace on which the quota is being assigned.
Example:
ALTER USER john_doe
QUOTA 100M ON users;
This command assigns a 100MB quota on the `users` tablespace to the user `john_doe`.
- Unlimited Quota: If a user requires unrestricted space on a specific tablespace, use the `UNLIMITED` keyword:
ALTER USER username
QUOTA UNLIMITED ON tablespace_name;
- Removing Quota: To remove a quota, set it to `0`:
ALTER USER username
QUOTA 0 ON tablespace_name;
- Verification: After assigning quotas, verify the allocation by querying the `DBA_TS_QUOTAS` view to ensure the quotas are correctly set.
Example SQL query:
SELECT * FROM DBA_TS_QUOTAS
WHERE USERNAME = 'username';
Replace `username` with the name of the user whose quotas need verification.
Effectively assigning and managing disk quotas is integral for the administration of Oracle Database 12c. It ensures efficient usage of available disk space, preventing any unauthorized or unintended space utilization. Regularly monitor and adjust the quotas to align with the changing requirements and ensure the seamless operation of the database environment.
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.
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.
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.
Tablespaces Quotas - Quiz
