tomcat黑窗口报错信息
Caused by: java.sql.SQLException: ORA-04030: 在尝试分配 4186136 字节 (QERHJ hash
-joi,QERHJ list array) 时进程内存不足 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedSta
tement.java:952)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStateme
nt.java:1054)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPrepa
redStatement.java:836)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:1124)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep
aredStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePrepare
dStatement.java:3329)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(Dele
gatingPreparedStatement.java:93)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:
208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Lo
ader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 141 more
场景描述:查询数据量大的报表时报错;
核心错误:ORA-04030: 在尝试分配 4186136 字节 (QERHJ hash-joi,QERHJ list array) 时进程内存不足
分析策略:
1,分析oracle的sga
2,查看oracle安装目录的硬盘存储剩余大小;
3,分析oracle表空间剩余:有的没有配置空间自增,或者自增配置失败;
3.1,具体操作:
查询每个用户下面剩余表空间,可以看到uses表空间剩余零点几;
select dbf.tablespace_name,dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf, (select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace, sum(tt.blocks) freeblocks
from dba_free_space tt group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
3.2,增加表空间:手动增加,或者配资为自动增加;
3.2.1,手动增加,以此增加原来的一倍左右;
select * from dba_data_files where tablespace_name = 'USERS';--查询路径
alter tablespace USERS add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HTCP\USERS02.DBF' size 5000m; --现场
3.2.2,自动增加
查看表空间是否自动增长
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;
设置表空间自增:
alter database datafile '\oracle\oradata\user.dbf' autoextend on next 100m maxsize 10000m
4,测试验证;ok,问题解决。