How to Grant Quota Unlimited on Tablespace to User

 To grant quota on tablespace to users there are two options.

Specific Tablespace

SQL> create user webuser identified by 'backup hash key' default tablespace USERS temp tablespace TEMP;

User created.

SQL> alter user webuser quota unlimited on users;

User altered.

SQL> column tablespace_name format a30;

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 = 'WEBUSER';

TABLESPACE_NAME      QUOTA(GB)
-------------------- ----------
USERS                UNLIMITED

Revoke Tablespace Quota

To quota off, just use 0 to revoke its usage on the tablespace.

SQL> alter user webuser quota 0 on USERS;

User altered.

To check as below:

SQL> select * from dba_ts_quotas where username = 'WEBUSER' and tablespace_name = 'USERS';

no rows selected

Another way to revoke the access:

SQL> revoke unlimited tablespace from erpapp;

Revoke succeeded.

Quota Unlimited

To open the limitation use UNLIMITED instead of an explicit value.

SQL> alter user webuser quota unlimited on users;

User altered.

All Unlimited Tablespace

SQL> grant unlimited tablespace to webuser;

Grant succeeded.

The user has the right to use any tablespace without any limitation.

Below DDL commands are useful :

SQL> SELECT DBMS_METADATA.GET_DDL('USER',username) as script from dba_users where username='&username';

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SSO_ID') from dual;

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SSO_ID') from dual;