Grant Tablespace Quota
Since quota usage limitation is not an object privilege, we can't grant it like this:
SQL> grant quota 100g on erpapp_tbs_01 to erpapp;
grant quota 100g on erpapp_tbs_01 to erpapp
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
OK, we saw error ORA-00990, which means that we can't treat it like a normal privilege, then how can we do it?
Solutions
To grant quota on tablespace to users, you have two options.
Specific Tablespace
In fact, quota limitation on a specific tablespace is rather an attribute to the user than a privilege. So here comes the formal solution, which is adding an attribute to the user. Let's see some examples.
Quota 100g
SQL> alter user erpapp quota 100g on erpapp_tbs_01;
User altered.
To check the result, we perform a query on DBA_TS_QUOTAS.
SQL> column tablespace_name format a20;
SQL> column "QUOTA(GB)" format a10;
SQL> select tablespace_name, case max_bytes when -1 then 'UNLIMITED' else to_char(max_bytes/1024/1024/1024) end "QUOTA(GB)" from dba_ts_quotas where username = 'ERPAPP';
TABLESPACE_NAME QUOTA(GB)
-------------------- ----------
ERPAPP_TBS_01 100
USERS UNLIMITED
To revert the process, we can revoke quota on specific tablespace form the user.
Quota Unlimited
To open the limitation, you can also use UNLIMITED instead of an explicit value.
SQL> alter user erpapp quota unlimited on erpapp_tbs_01;
User altered.
But the unlimited space usage is limited by this tablespace. Next, we introduce a way to fully open space usage to users.
All Unlimited Tablespace
To fully open space usage to an user, you should use UNLIMITED TABLESAPCE privilege. Yes, it's a privilege.
SQL> grant unlimited tablespace to erpapp;
Grant succeeded.
The privilege overrides all tablespace quotas you set in the first solution. Now, the user has the right to use any tablespace without any limitation.
To revert the process, we can revoke quota on all tablespaces form the user.