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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.