Skip to content
Home » Oracle Database » Grant Quota Unlimited on Tablespace to User

Grant Quota Unlimited on Tablespace to User

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.

Leave a Reply

Your email address will not be published. Required fields are marked *