Tablespace full, error codes and autoexend mode in Oracle

Many times applications write on tablespaces util tablespace become full and application doesn't work anymore and blocks.
Probably you should define  some purge policy for your data (to avoid database growing more and more); anyway to fix quicly the problem you have to assign new datafile to full tablespace.
To find out what tablespace is  full you can see in many ways.

1- Check in your application logs for messages  like ORA-  (case hibernate usate as ORM engine here)
 Caused by: org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
        at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:222)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2229)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2665)
        at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:60)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
        at org.hibernate.ejb.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:296)
        ... 134 more
Caused by: java.sql.BatchUpdateException: ORA-01653: unable to extend table INEM_REP.STRUCT_VALUE_ATTRIBUTE by 512 in tablespace EMXDATA01
        at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)
        at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10720)
        at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:774)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)



2 -Otherwise you may check for ORA-1691 in database alert.log file

ORA-1691: unable to extend lobsegment IEMX_APP.SYS_LOB0000044004C00006$$ by 512 in tablespace              EMXDATA01
ORA-1691: unable to extend lobsegment IEMX_APP.SYS_LOB0000044004C00006$$ by 512 in tablespace              EMXDATA01
ORA-1691: unable to extend lobsegment IEMX_APP.SYS_LOB0000044004C00006$$ by 512 in tablespace              EMXDATA01
ORA-1691: unable to extend lobsegment IEMX_APP.SYS_LOB0000044004C00006$$ by 512 in tablespace              EMXDATA01
ORA-1691: unable to extend lobsegment IEMX_APP.SYS_LOB0000044004C00006$$ by 512 in tablespace              EMXDATA01
ORA-1691: unable to extend lobsegment IEMX_APP.SYS_LOB0000044004C00006$$ by 512 in tablespace              EMXDATA01
ORA-

Now you know what is the full tablespace (EMXDATA01 in my sample)

To find out datafile settings for your tablespace connect to database as sysdba:

# sqlplus  / as sysdba
# SQL>  select TABLESPACE_NAME, FILE_NAME,    AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME like 'E%';

TABLESPACE_NAME              FILE_NAME                              AUT
EMXDATA01                        /oradata/iNEM/emxdata01.dbf      NO
This tablespace has no autoendend mode enabled, so now quickest solution is provide an extra datafile for tablespace
       
# SQL>ALTER TABLESPACE EMXDATA01 ADD DATAFILE  /oradata/iNEM/emxdata02.dbf    size 1000m


A SAMPLE SCRIPT TO ANALIZE TABLESPACE DATA CONTENT

Edit  a file named singletspace.sql as follows (this file has an input parameter which is tablespace name ):

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
column "MAX (MB)"  format 9999990D99
column "USED (MB)" format 9999990D99
column "FREE (MB)" format 9999990D99
column "FREE (%)"  format 990D99
column "TABLESPACE_NAME" format A16
select a.tablespace_name, a.max_mb "MAX (MB)",
       (a.max_mb - b.free_mb) "USED (MB)",
       b.free_mb "FREE (MB)",b.free_mb/a.max_mb*100 "FREE (%)"
from
(select substr(tablespace_name,1,20) tablespace_name,
        sum(bytes)/(1024*1024) max_mb
from dba_data_files
group by tablespace_name
) a,
(select
substr(tablespace_name,1,20) tablespace_name,
sum(b.bytes)/(1024*1024) free_mb
from dba_free_space b
group by tablespace_name
) b
where a.tablespace_name=b.tablespace_name
order by 5 asc
/



Then 0pen a SQLPLUS connection to Oracle as sysdba

sqlplus / as sysdba
SQL>@singletspace



ANOTHER USEFULL SQL FILE

EDIT FILE NAMED tablesizebytablespace.sql

COLUMN MBYTES FORMAT 9999990D99
SELECT RPAD(SEGMENT_NAME,29,' ') OBJECT_NAME, RPAD(SEGMENT_TYPE,3,' ') TYPE, RPAD(OWNER,7,' ') OWNER, BYTES/1048576 MBYTES from DBA_SEGMENTS WHERE TABLESPACE_NAME=upper('&1') ORDER BY SEGMENT_NAME;

Run this file as follow:

sqlplus / as sysdba
   spool 06_06_2012_tables.txt
 
   @tablesizebytablespace.sql 'EMXDATA01'
   spool off