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='DBACLASS';

TABLESPACE_NAME         USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024
----------------------- -------- --------------- -------------------
SMALLTS                 DBACLASS              12                  20

Here DBACLASS 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 DBACLASS quota 50M on SMALLTS;

User altered.

SQL>  select  tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024  from dba_ts_quotas where username='DBACLASS';


TABLESPACE_NAME         USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024
----------------------- -------- --------------- -------------------
SMALLTS                 DBACLASS              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='DBACLASS';


TABLESPACE_NAME         USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024
----------------------- -------- --------------- -------------------
SMALLTS                 DBACLASS              24                  50

Object created successfully.

 

SEE ALSO – > TABLESPACE QUOTA INFORMATION