3.2 How do I…Grant and determine user disk quotas?Problem
Some developers in my organization like to create huge tables when they test their applications. Because we have a limited amount of space in the database, I need to limit the amount of disk space a user account can occupy. How can I restrict a user account’s disk space?
Technique
The QUOTA clause of the ALTER USER statement is used to restrict the disk space of an existing user account. The disk space can be restricted for a new user account by specifying a QUOTA clause in the CREATE USER statement. A user account is restricted storage by tablespace.
Steps
1. Run SQL*Plus and connect to the database as the WAITE user account. CHP3_3.sql, shown in Figure 3.2, contains three ALTER USER statements to restrict disk usage in the SYSTEM tablespace.
The three CREATE USER statements create the user accounts that will be restricted by the ALTER USER statements. The user account WILMA is restricted to 10MB, BETTY to 500KB, and BARNEY to 100,000 bytes in the SYSTEM tablespace.
2. Execute CHP3_3.sql to create the user accounts and restrict their disk space.
SQL> START CHP3_3.sql
User created.
User created.
User created.
User altered.
User altered.
User altered.
How It Works
The QUOTA clause of the CREATE USER and ALTER USER statements limits the amount of disk space a user account’s objects can occupy. The first three statements in CHP3_3.sql create user accounts that will be restricted by the following statements. The first ALTER USER account restricts the WILMA user account to 10MB, represented with the statement 10M. The user account BETTY is restricted to 500KB, represented by 500K. If M or K is not specified, the statement interprets the value in bytes. The BARNEY user account is restricted to 100,000 bytes.
Comments
Creating disk quotas is mainly an issue for developers and users running decision support applications that create summary tables. The UNLIMITED TABLESPACE privilege gives a user account unlimited space within all tablespaces and ignores the quotas placed on the user account. For example, ALTER USER WILMA QUOTA UNLIMITED ON SYSTEM would give the WILMA user account unlimited space on the SYSTEM tablespace, and GRANT UNLIMITED TABLESPACE TO WILMA would give unlimited space on all tablespaces to the WILMA user account.