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

You May Also Like

Blackout targets in OEM 12c cloud control
oracleasm utility for ASMLIB
how to change archivelog destination in oracle
Generate custom report from OEM cloud control
Flashback primary database in dataguard environment
Apply patch on oracle 12.2 database ( Release update)
Apply database patch from OEM Cloud control
How to install postgres database on mac os/linux
Shell script for monitoring Alert log
how to send mail using utl_mail in oracle 11g

From This Website

How to change spfile in Oracle RAC.
Change dbsnmp password for target db in oem 12c
PDB Lockdown Profiles in Oracle 12.2
Useful TFACTL commands
How to run expdp in pluggable database(PDB)
how to change archivelog destination in oracle
Unified audit trail in Oracle 12c
How to Read CSV files in R studio
How to enable flash recovery area in oracle database
How to setup dataguard broker configuration (DG broker) in 12c