PROBLEM:
While creating a table or inserting data, user is getting error ORA-01536: space quota exceeded for tablespace .
SQL> create table OBJCOPY tablespace SMALLTS as select * from dba_objects;
create table OBJCOPY tablespace SMALLTS as select * from dba_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SMALLTS'
SOLUTION:
Tablespace quota is the storage allocated for an user in a tablespace. Once the user reaches the max allocated space it will throw error like this.
SQL> select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username='EXATREE';
TABLESPACE_NAME USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024
----------------------- -------- --------------- -------------------
SMALLTS EXATREE 12 20
Here EXATREE user can use maximum upto 20MB space of SMALLTS table space. Currently it used upto 12MB.
SQL> create table OBJCOPY tablespace SMALLTS as select * from dba_objects;
create table OBJCOPY tablespace SMALLTS as select * from dba_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SMALLTS'
Now to fix it , increase the quota for that user.
SQL> alter user EXATREE quota 50M on SMALLTS;
User altered.
SQL> select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username='EXATREE';
TABLESPACE_NAME USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024
----------------------- -------- --------------- -------------------
SMALLTS EXATREE 12 50
Now create the object
SQL> create table OBJCOPY tablespace SMALLTS as select * from dba_objects;
Table created.
SQL> select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username='EXATREE';
TABLESPACE_NAME USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024
----------------------- -------- --------------- -------------------
SMALLTS EXATREE 24 50
Object created successfully.
SEE ALSO - > TABLESPACE QUOTA INFORMATION
ORA-tablespace TROUBLESHOOTING