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

You May Also Like

shell script for file system alert
OUTBOUND_DBLINK_PROTOCOLS in oracle 12.2
ORA-30034: Undo tablespace cannot be specified as temporary tablespace
How to find weblogic version in oracle weblogic 12
How to generate ADDM report
How to change sysman password for oracle 12c cloud control
TNS-12542: TNS:address already in use
Deinstall Management Agents Oracle 12c cloud control
FLASHBACK parameter in DATAPUMP(EXPDP)
Hive installation with mysql database

From This Website

How to start MySQL on Linux and Windows
How to recreate physical standby controlfile
Shell script to delete old archives using RMAN
How to find weblogic version in oracle weblogic 12
COLS & COLSEXCEPT FILTER in goldengate
Apply database patch from OEM Cloud control
How to install oracle client in silent mode using response file
DBMS_PARALLEL_EXECUTE in oracle PL/SQL
Shell script to report failed login attempt in oracle
oraversion utility in oracle 18c - New feature