最近在帮香港地铁在做一个数据转换，老的系统的表数据量都在几千万级别的，转换到700万左右就会出现以下错误：

2011/06/23 06:14:20 - PM_MEASUREMENT_READING.0 - linenr 7550000

2011/06/23 06:14:20 - Update.0 - linenr 7550000

2011/06/23 06:14:27 - BU_ID.0 - LineNr : 15150000

2011/06/23 06:14:37 - EQUIP_ID.0 - linenr 7600000

2011/06/23 06:15:06 - CONV_EAM_METER_READING.0 - linenr 7650000

2011/06/23 06:15:06 - MEASUREMENT_ID.0 - linenr 7600000

2011/06/23 06:15:46 - Add sequence.0 - linenr 7650000

2011/06/23 06:15:46 - BU_ID.0 - LineNr : 15200000

2011/06/23 06:15:46 - Switch 1.0 - linenr 7600000

2011/06/23 06:15:46 - Judge.0 - linenr 7600000

2011/06/23 06:15:46 - Switch 2.0 - linenr 7600000

2011/06/23 06:16:35 - Convert LAST_UPD_USER_ID.0 - linenr 7650000

2011/06/23 06:16:35 - Filter rows.0 - linenr 7600000

2011/06/23 06:17:34 - STATUS.0 - linenr 7650000

2011/06/23 06:17:35 - PM_MEASUREMENT_READING.0 - linenr 7600000

2011/06/23 06:17:35 - Update.0 - linenr 7600000

2011/06/23 06:17:41 - BU_ID.0 - LineNr : 15250000

2011/06/23 06:17:51 - EQUIP_ID.0 - linenr 7650000

2011/06/23 06:18:21 - CONV_EAM_METER_READING.0 - linenr 7700000

2011/06/23 06:18:21 - MEASUREMENT_ID.0 - linenr 7650000

2011/06/23 06:18:59 - Add sequence.0 - linenr 7700000

2011/06/23 06:18:59 - BU_ID.0 - LineNr : 15300000

2011/06/23 06:18:59 - Switch 2.0 - linenr 7650000

2011/06/23 06:18:59 - Switch 1.0 - linenr 7650000

2011/06/23 06:18:59 - Judge.0 - linenr 7650000

2011/06/23 06:19:32 - PM_MEASUREMENT_READING.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Because of an error, this step can't continue:

2011/06/23 06:19:32 - PM_MEASUREMENT_READING.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:

2011/06/23 06:19:32 - PM_MEASUREMENT_READING.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Unable to roll back database transaction to savepoint

2011/06/23 06:19:32 - PM_MEASUREMENT_READING.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : ORA-04030: 在尝试分配 484 字节 (session heap,kxsc: kkspsc0) 时进程内存不足

2011/06/23 06:19:32 - PM_MEASUREMENT_READING.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.database.Database.rollback(Database.java:4766)

2011/06/23 06:19:32 - PM_MEASUREMENT_READING.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:405)

2011/06/23 06:19:32 - PM_MEASUREMENT_READING.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:117)

2011/06/23 06:19:32 - PM_MEASUREMENT_READING.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)

2011/06/23 06:19:32 - PM_MEASUREMENT_READING.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : java.lang.Thread.run(Thread.java:619)

2011/06/23 06:19:32 - Get Variables 1.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)

2011/06/23 06:19:32 - Judge.0 - Finished processing (I=0, O=0, R=7656961, W=15313922, U=0, E=0)

2011/06/23 06:19:32 - CONV_METER - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Errors detected!

2011/06/23 06:19:32 - Switch 2.0 - Finished processing (I=0, O=0, R=7656961, W=0, U=0, E=0)

2011/06/23 06:19:32 - CONV_METER - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Errors detected!

2011/06/23 06:19:32 - Dummy (do nothing).0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)

2011/06/23 06:19:32 - Switch 1.0 - Finished processing (I=0, O=0, R=7656961, W=1, U=0, E=0)

2011/06/23 06:19:32 - Get Variables.0 - Finished processing (I=0, O=0, R=3, W=3, U=0, E=0)

2011/06/23 06:19:32 - Get Variables 3.0 - Finished processing (I=0, O=0, R=7636958, W=7636958, U=0, E=0)

2011/06/23 06:19:32 - Update.0 - Finished processing (I=7636958, O=0, R=7636958, W=7636958, U=7636958, E=0)

2011/06/23 06:19:32 - Filter rows.0 - Finished processing (I=0, O=0, R=7646961, W=7646961, U=0, E=0)

2011/06/23 06:19:32 - BU_ID.0 - Finished processing (I=0, O=0, R=7656962, W=15313924, U=0, E=0)

2011/06/23 06:19:32 - CONV_EAM_METER_READING.0 - Finished reading query, closing connection.

2011/06/23 06:19:32 - Add sequence.0 - Finished processing (I=0, O=0, R=7706967, W=7706967, U=0, E=0)

2011/06/23 06:19:32 - MEASUREMENT_ID.0 - Finished processing (I=15333925, O=0, R=7666963, W=7666963, U=0, E=0)

2011/06/23 06:19:32 - PM_MEASUREMENT_READING.0 - Finished processing (I=0, O=7636958, R=7636959, W=7636958, U=0, E=4)

2011/06/23 06:19:32 - EQUIP_ID.0 - Finished processing (I=15353928, O=0, R=7676964, W=7676964, U=0, E=0)

2011/06/23 06:19:32 - STATUS.0 - Finished processing (I=15373930, O=0, R=7686965, W=7686965, U=0, E=0)

2011/06/23 06:19:32 - Convert LAST_UPD_USER_ID.0 - Finished processing (I=15393932, O=0, R=7696966, W=7696966, U=0, E=0)

2011/06/23 06:19:32 - CONV_METER - CONV_METER

2011/06/23 06:19:32 - CONV_EAM_METER_READING.0 - Finished processing (I=7716969, O=0, R=0, W=7716968, U=0, E=0)

2011/06/23 06:19:32 - CONV_METER - CONV_METER

2011/06/23 06:19:32 - FAIL_RECORDS 1.0 - Finished processing (I=0, O=1, R=1, W=0, U=0, E=0)

2011/06/23 06:19:32 - FAIL_RECORDS.0 - Finished processing (I=0, O=3, R=3, W=0, U=0, E=0)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

我发现每次报这个错误的时候oracle服务器上面的oracle.exe进程到了1.7g左右，奇怪的是每次都在这个地方就死了，上网查了以下，说是windows环境下oracle有1.7g的内存限制，可以突破限制，但是就算突破了限制，调到2g又有什么用，最多转到1000万有会报这个错误了，转换过程中，oracle进程所占用的内存在不断的增加，我的转换设置的是1000条commit一次的，有没有办法一边转换一边释放掉已经commit了的内存，不然的话要转几千万的数据那oracle进程占用起码要5g左右，kettle里面有没有什么设置可以释放内存的？ 我发现只有转换停止了之后oracle进程才会释放，各位大哥请问有没有什么办法在转换大数据量时不会报以上的错误呢？ 我试过很多oracle的调优，把最大连接数设大、sga和pga都设大了都没效果，反而设大了之后转到400万左右就挂了，各位大哥知道的话请联系我：

qq：263812212

邮箱：263812212@qq.com

2011年6月22日 23:30