How to resolve “ORA-01536 : space quota exceeded for tablespace ‘USERS’ ” error on Oracle

Reason for Error

Before understanding the root cause of the above error, it is necessary to understand the term tablespace indicated in the error

Every database in Oracle is stored into a storage unit which is termed as Tablespace.
When a user is created in Oracle, he is assigned a tablespace and a size of that tablespace.

Every database or table created by the user goes into the tablespace assigned to him. Besides all the data inserted into the tables by a user also consumes the tablespace assigned to him.

When the data(database + tables + data) for a user exceeds his tablespace quota, this error arises.

From Oracle documentation, cause of ORA-01536 is

The space quota for the segment owner in the tablespace has been exhausted and the operation attempted the creation of a new segment extent in the tablespace.

A user is assigned a tablespace at the time of creation of user in the Create User SQL query.

If a user is not assigned a tablespace and he creates a table, it goes into SYSTEM tablespace.

Solution

There are two solutions to remove the above error:

  1. rease the size of the tablespace allocated to a user. This requires executing the below SQL query on the Oracle database client. 
    ALTER USER <user_name> quota unlimited on USERS;

    Substitute the user_name with the name of user for which the tablespace quota needs to be increased.

    For allocating a fixed space such as 20MB to a user, replace unlimited with the specified amount in the query as

    ALTER USER <user_name> quota 20M on USERS;
  2. Drop the user(if possible), recreate the user and assign a very large table space quota to the new user.
    Table space quota during user creation can be accomplished using the below query :

    CREATE USER <user_name>
 IDENTIFIED BY <user_password>
 DEFAULT TABLESPACE <tablespace_name>
 TEMPORARY TABLESPACE <tablespace_name>
 QUOTA 20M on <tablespace_name>;

    Replace user name, password and tablespace name in above query with the relevant values.

Leave a Reply