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

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

You May Also Like

SKIP_CONSTRAINT_ERRORS as DATA_OPTION in impdp
ORA-32773: operation not supported for smallfile tablespace
How to Read CSV files in R studio
Steps for changing public hostname for a standalone grid infrastructure
How to find weblogic version in oracle weblogic 12
Generate custom report from OEM cloud control
How to create encrypted tablespace in PDB( oracle 12c)
orapwd tool for password file in oracle
How to multiplex control file in standalone database
COLS & COLSEXCEPT FILTER in goldengate

From This Website

OUTBOUND_DBLINK_PROTOCOLS in oracle 12.2
How to change spfile in Oracle RAC.
ESTIMATE REQUIRED DISK SPACE FOR EXPORT USING estimate_only
How to generate AWR report in RAC
How to drop a database in postgres
_optimizer_ignore_hint
Find sessions consuming lot of CPU
Shell script to delete old archives using RMAN
How to move controlfile to a new location in oracle
how to change archivelog destination in oracle