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

You May Also Like

How to recreate MGMT database in ORACLE 12C GRID
sec_case_sensitive_logon parameter in oracle
How to run expdp in pluggable database(PDB)
How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT
How to rename Columns in R
TNS-12542: TNS:address already in use
DBMS_PARALLEL_EXECUTE in oracle PL/SQL
How to multiplex control file in standalone database
Deinstall Management Agents Oracle 12c cloud control
How to change spfile in Oracle RAC.

From This Website

Lock account automatically with INACTIVE_ACCOUNT_TIME
oraversion utility in oracle 18c - New feature
How to drop a database in postgres
Display sql_id of the sql, using set feedback in oracle 18C - New feature
Multinode Hadoop installation steps
How to enable flash recovery area in oracle database
Steps for upgrading weblogic 12.1.2 to 12.1.3
How to install trace file analyzer( TFACTL)
How to add a tempfile in primary database in dataguard
ORA-16855: transport lag has exceeded specified threshold