关于oracle 表空间满32G了怎么处理的问题
最近客户反馈线上服务报错，无法使用，查看日志内容：
Caused by: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [insert into equip_temp_data (col_code, col_date, col_type, col_value, em_code, gid) values (?, ?, ?, ?, ?, ?)/** NST **/]; SQL state [72000]; error code [1653]; Could not execute JDBC batch update; nested exception is org.hibernate.QueryTimeoutException: Could not execute JDBC batch update at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:651) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:105) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:523) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631) at com.epichust.service.erp.ErpService$$EnhancerByCGLIB$$cad9d843.dealWithEquipTempData(<generated>) ... 7 more Caused by: org.hibernate.QueryTimeoutException: Could not execute JDBC batch update at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:124) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:268) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1222) at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:389) at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133) at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:76) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:519) ... 17 more Caused by: java.sql.BatchUpdateException: ORA-01653: 表 UNIMAX_JSDJ_CS.EQUIP_TEMP_DATA 无法通过 1024 (在表空间 MESTAR_DATA 中) 扩展 at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10345) at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230) at com.alibaba.druid.filter.FilterChainImpl.statement_executeBatch(FilterChainImpl.java:2596) at com.alibaba.druid.filter.FilterAdapter.statement_executeBatch(FilterAdapter.java:2474) at com.alibaba.druid.filter.FilterEventAdapter.statement_executeBatch(FilterEventAdapter.java:279) at com.alibaba.druid.filter.FilterChainImpl.statement_executeBatch(FilterChainImpl.java:2594) at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.executeBatch(StatementProxyImpl.java:192) at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeBatch(DruidPooledPreparedStatement.java:559) at net.sf.log4jdbc.StatementSpy.executeBatch(StatementSpy.java:523) at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70) at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268) ... 26 more
查看Oracle错误代码，是因为Oracle表空间不足导致的，用SQL查看表空间使用率：
SELECT UPPER( F.TABLESPACE_NAME ) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR( ROUND( ( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2 ), '990.99' ) "使用比", F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)" FROM ( SELECT TABLESPACE_NAME, ROUND( SUM( BYTES ) / ( 1024 * 1024 ), 2 ) TOTAL_BYTES, ROUND( MAX( BYTES ) / ( 1024 * 1024 ), 2 ) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, ( SELECT DD.TABLESPACE_NAME, ROUND( SUM( DD.BYTES ) / ( 1024 * 1024 ), 2 ) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME ) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;
发现表空间使用率100%，只剩下1.25M空间可用了，难怪，但是我设置了表空间自动扩容啊，查看表空间是否自动扩容：
SELECT t.tablespace_name, d.file_name, d.autoextensible, d.increment_by, d.bytes, d.maxbytes, d.status FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name ORDER BY tablespace_name, file_name;
表空间确实是自动扩容的，但是到了32G就不扩容了，后来百度知道Oracle单个表空间文件最大就到32G，那到了32G就不能再扩容了吗？有方法，再追加表空间文件就可以：
ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE 'E:\ORACLE_DB\TABLESPACE_EX1.DBF' SIZE 1000M AUTOEXTEND ON NEXT 500M;
其实扩展表空间大小有两种方式：1.追加表空间文件； 2.创建 bigfile 大表空间文件；第二种方式会操作原来的表空间数据，对于线上环境来说，能不改动是最好的，所以我选择了第一种方式，追加表空间文件。
