ORA-01536: space quota exceeded for tablespace ‘USERS’

ORA-01536: space quota exceeded for tablespace ‘USERS’

Errors
While inserting the data into the table by connection with application user.

SQL> insert into test values(2);
insert into test values(2)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

Same reason for error:

SQL> insert into test values(1);
insert into test values(1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Cause
User does not have permission to write in Tablespace “USERS” more than allocated Quota. You need to increase the quota value.

Solution
Need to increase the Quota value that was allocated.

1. Login with admin users.

SQLPLUS sys@pdb2 as sysdba
password

2. Check the assigned Quota limit for the user on tablespaces.
Assigned quota limit in example is 10 MB to user.

SQL> select (bytes)/1024/1024,MAX_BYTES/1024/1024 from dba_ts_quotas where username='TEST';

(BYTES)/1024/1024 MAX_BYTES/1024/1024
----------------- -------------------
                2                  10

3. Increase the quota limit or you can increase quota to unlimited also.

-- Increase from 10m to 200m
alter user test quota 200m on users;
User altered.

--Increase to unlimited
SQL> alter user test quota unlimited on users;
User altered.

4. Verify after increase the quota limit to unlimited.

SQL> select (bytes)/1024/1024,MAX_BYTES/1024/1024 from dba_ts_quotas where username='TEST';

(BYTES)/1024/1024 MAX_BYTES/1024/1024
----------------- -------------------
                2          -9.537E-07

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.