Tag Archives: Set tablespace quota to user

Check USER Tablespace quota limit in Oracle

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;