ORA-01536: Space Quotas Exceeded for Table Space for a Particular User

 ORA-01536: Space Quotas Exceeded for Table Space for a Particular User

Problem

while creating the table or insert the data values into the table user is getting an error  ORA-01536 :space quota exceeded for tablesapce

sql> create table accountmaster tablespace smalltbs as select * from dba_objects;

create table accountmaster tablespace smalltbs as select * from dba_objects;

*

ERROR at line 1;

ORA-01536 : space quota exceeded for tablespace 'SMALLTBS'

Solution

Table space quota is the allocated in a particular user in tablespace, Once the particular user reaches the max allocated space it shows error like this

SQL>select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username like 'CHAITANYA';

TABLESPACE_NAME       USER_NAME  BYTES/1024/1024   MAX_BYTES /1024/1024

-------------------------------------------------------------------------------------------------------------

SMALLTBS                         CHAITANYA               18                          25

Here CHAITANYA user can have maximum upto 25 MB space  of SMALLTBS tablespace but currently  it reaches used upto 18 MB

SQL> create table accountmaster tablespace smalltbs as select * from dba_objects;

create table accountmaster tablespace smalltbs as select * from dba_objects;

*

ERROR at line 1;

ORA-01536 : space quota exceeded for tablespace 'SMALLTBS'

Let us start the  process to start the fix it and increase the quota for that user 'CHAITANYA'

SQL> alter user CHAITANYA quota 100M on SMALLTBS;

user altered

SQL>select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username like 'CHAITANYA';

TABLESPACE_NAME       USER_NAME  BYTES/1024/1024   MAX_BYTES /1024/1024

-------------------------------------------------------------------------------------------------------------

SMALLTBS                         CHAITANYA               18                         100

Now create the table and insert the table values into that particular user

SQL> create table accountmaster tablespace smalltbs as select * from dba_objects;

create table accountmaster tablespace smalltbs as select * from dba_objects;

table created sucessfully

SQL>select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username like 'CHAITANYA';

TABLESPACE_NAME       USER_NAME  BYTES/1024/1024   MAX_BYTES /1024/1024

-------------------------------------------------------------------------------------------------------------

SMALLTBS                         CHAITANYA               30                        100

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME