Skip to content

Grant Quota on Tablespace to User

  • by

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.

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

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 the 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 on this tablespace. Next, we introduce a way to fully open space usage to users.

2. All Tablespace

To fully open space usage to an user, you should use UNLIMITED TABLESAPCE 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.

Leave a Reply

Your email address will not be published.