Check user Tablespace quota limit in Oracle

Tablespace quota is assigned to user for utilized the space limit in Oracle tablespace.

Check the user tablespace Quota limit in Oracle
Note: -1 means in MAX_BYTES column: unlimited

set line 200 pages 200
col tablespace_name for a15
col username for a17
col bytes for 9999999
SELECT * FROM DBA_TS_QUOTAS where username = 'HR';

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
--------------- ----------------- -------- ---------- ---------- ---------- ---
SYSAUX          HR                 1900544         -1        232         -1 NO
USERS           HR                       0  104857600          0      12800 NO

Grant user permission for tablespace quota

ALTER USER "user_name" QUOTA UNLIMITED ON tablespace_name;


-- assign HR user quota of 100 MB on user tablespace
Alter user HR quota 100M on USERS;


Alter user commands in Oracle
Change existing setting of user by using alter command.

ALTER USER user1 IDENTIFIED BY new_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M on users
QUOTA 100M on temp
PROFILE hr;