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


Related Topics

ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
ORA-01536: space quota exceeded for tablespace
ORA-32773: operation not supported for smallfile tablespace
ORA-30034: Undo tablespace cannot be specified as temporary tablespace
TNS-12542: TNS:address already in use
ORA-16855: transport lag has exceeded specified threshold
ORA-32774: more than one file was specified for bigfile tablespace
ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
TNS-01106: Listener using listener name has already been started
ORA-32771: cannot add file to bigfile tablespace

You May Also Like

Generate custom report from OEM cloud control
ORA-16855: transport lag has exceeded specified threshold
Apply database patch from OEM Cloud control
ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
how to send mail using utl_mail in oracle 11g
How to use expdp to export data from physical standby database
How to install postgres database on mac os/linux
sec_case_sensitive_logon parameter in oracle
Find user commits per minute in oracle database
LOG_CHECKPOINTS_TO_ALERT parameter in oracle

From This Website

ORA-16855: transport lag has exceeded specified threshold
Generate custom report from OEM cloud control
Oracle database Security Assessment Tool
Shell script to monitor goldengate process
Unified audit trail in Oracle 12c
SAMPLE parameter in EXPDP to export subset of data
How to change spfile in Oracle RAC.
How to enable flash recovery area in oracle database
How to create encrypted tablespace in PDB( oracle 12c)
DEFERRED_SEGMENT_CREATION parameter in oracle