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
