某生产数据库，不能登录，数据库不能归，生产环境为IBM小机，oracle为单实例的10.2.0.4。登录到数据库服务器可以看到磁盘空间被占用完了,归档日志存储在yb_oradata目录下，Oracle软件安装在oracle目录，用户不能登录是因为不能写审计日志，业务不能办理是因为归档目录没有空间，这些是问题的症状。，一般oracle所占的大小不可能达到上百G的大小。那就需要检查是什么文件占用了空间,生产环境为IBM小机。
[IBMP740-1:root:/]#df -g Filesystem GB blocks Free %Used Iused %Iused Mounted on /dev/hd4 8.00 4.03 50% 11099 2% / /dev/hd2 6.00 3.06 49% 57334 8% /usr /dev/hd9var 16.00 6.87 58% 11305 1% /var /dev/hd3 10.00 8.38 17% 2008 1% /tmp /dev/fwdump 1.00 1.00 1% 5 1% /var/adm/ras/platform /dev/hd1 2.00 2.00 1% 5 1% /home /dev/hd11admin 10.00 10.00 1% 5 1% /admin /proc - - - - - /proc /dev/hd10opt 2.00 1.65 18% 11518 3% /opt /dev/livedump 2.00 2.00 1% 4 1% /var/adm/ras/livedump /dev/oracle_lv 100.00 0.00 100% 66389 84% /oracle /dev/bak_lv 999.00 520.64 48% 29 1% /bak /dev/yboradata_lv 1399.00 0 100% 337 1% /yb_oradata
可以看到admin目录占用了79.16G
[IBMP740-1:root:/oracle]#du -sg * 0.00 Mail 79.16 admin 0.00 chapter10_01.sql 0.04 flash_recovery_area 3.06 inst 0.00 jd.log 0.00 lost+found 0.00 oraInventory 8.77 product 0.00 smit.log 0.00 smit.script 0.00 smit.transaction 0.00 sosi.txt 0.00 spcusr.lis 0.00 sqlhc 0.00 sqlnet.log 0.04 sqlt 0.00 sqlt.zip 0.01 sqlt_s10819 0.00 sqlt_s34882_log.zip 0.00 sqlt_s34883_log.zip 0.00 sqlt_s34884_xecute.zip 0.00 sqlt_s34885_xecute.zip 0.00 sqlt_s34886_xecute.zip 0.00 sqlt_s34887_xecute.zip 0.00 test_high_version.txt
可以看到RLZY目录占用了79.16
[IBMP740-1:root:/oracle/admin]#du -sg * 0.00 CAIWU 0.00 ORCL 79.16 RLZY 0.00 chdyl
可以看到cdump与udump目录分别占用了40.94G,38.22G
[IBMP740-1:root:/oracle/admin/RLZY]#du -sg * 0.05 adump 1.66 bdump 40.94 cdump 0.00 dpdump 0.00 pfile 0.00 scripts 38.22 udump
adump ：审计信息
bdump ：后台进程trace 和alert log ,就是说 alert_sid.log也存在这个目录中
cdump ：一般放置一些核心的trace文件，内核实例缓冲区产生的跟踪文件,除非数据库出了问题 否则基本上不会有什么信息
dpdump：是存放一些登录信息的
pfile ：初始化参数文件 initSID
udump ：一般放置sql trace之后session的trace文件，用户服务器进程产生的跟踪文件，常见的是sql问题
那么我们先查看一下alert_RZLY.log
[IBMP740-1:root:/oracle/admin/RLZY]#tail -f /oracle/admin/RLZY/bdump/alert_RLZY.log ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] [] Wed Oct 26 19:22:35 2016 Thread 1 advanced to log sequence 47133 (LGWR switch) Current log# 2 seq# 47133 mem# 0: /yb_oradata/RLZY/RLZY/redo02.log Wed Oct 26 19:32:51 2016 Errors in file /oracle/admin/RLZY/udump/rlzy_ora_43647120.trc: ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] [] Wed Oct 26 19:42:40 2016 Errors in file /oracle/admin/RLZY/udump/rlzy_ora_13697930.trc: ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []
可以看到出现了ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []错误，具体的错误信息记录到/oracle/admin/RLZY/udump/rlzy_ora_13697930.trc文件中。如果查看该文件可以看到:
/oracle/admin/RLZY/udump/rlzy_ora_13697930.trc Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /oracle/product/10.2.0/db_1 System name: AIX Node name: IBMP740-1 Release: 1 Version: 6 Machine: 00F7AC3D4C00 Instance name: RLZY Redo thread mounted by this instance: 1 Oracle process number: 73 Unix process pid: 13697930, image: oracle@IBMP740-1 *** 2016-10-26 19:42:40.925 *** ACTION NAME:() 2016-10-26 19:42:40.915 *** MODULE NAME:(JDBC Thin Client) 2016-10-26 19:42:40.915 *** SERVICE NAME:(SYS$USERS) 2016-10-26 19:42:40.915 *** SESSION ID:(1249.559) 2016-10-26 19:42:40.915 Exception signal: 11 (SIGSEGV), code: 50 (Address not mapped to object), addr: 0xffffffffdfffff0, PC: [0x100be5d90, qcsAnalyzeBooleanExpr+0010] Registers: iar: 0000000100be5d90, msr: a00000000000d032 lr: 0000000100be5dd8, cr: 0000000048222020 r00: 0000000100be5dd8, r01: 0ffffffffe000080, r02: 000000011022a6e8, r03: 0ffffffffffee1a0, r04: 0000000110195798, r05: 00000001173f9a58, r06: 0000000000000001, r07: 0ffffffffffee1a0, r08: 0000000110457778, r09: 000000000000003f, r10: 0000000000000000, r11: 0000000000000000, r12: 00000001008c5ce0, r13: 0000000110275b80, r14: 070000059a4fae08, r15: 07000004dd304d00, r16: 0000000104d83ea8, r17: 07000004ada5ddd8, r18: 000000000000000a, r19: 07000004dd304c08, r20: 0000000110000a70, r21: 00000000000000d3, r22: 07000004dd3050e8, r23: 000003a1ecbd50bb, r24: 0000000000000001, r25: 07000004ab1466f8, r26: 0ffffffffffef590, r27: 0000000104fbef18, r28: 00000001104554a0, r29: 07000004ab1464f0, r30: 0000000119360ff8, r31: 00000001173f9a58, *** 2016-10-26 19:42:40.925 ksedmp: internal or fatal error ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] [] Current SQL statement for this session: update lv_handle_mdiindiacc_detail ld set ld.push_flag= '1' where ( ld.indipayser in (485506300,485504469,481520527,481520481, 481203712,484953649,484953901,481203462,481382692,481382673,485061110,485060889,481631327,481628371,481714509,481714502,485422790, 485422743,485505625,485504046,485505138,485422775,483924474,485422767,481537563,481537508,485422795,485422751,485710571,482042800, 482042766,485710537,485233344,485233182,485233274,481470443,481469597,485422785,485422737,481352719,481352131,485422780,485422731, 486517983,483676463,483677515,486516952,486517971,486516920,483676426,483677504,486517984,483676467,483677516,486516956,485834372, 485834335,486517621,483677148,486515894,483675382,482155166,482155007,485341981,485341686,485148741,485148592,485233418,485233219, 485691049,485690926,482024499,482024622,481430441,481429048,485422809,485422738,485003015,485002902,481365287,485108845,485110024, 481364104,481576480,481575936,485345250,481593493,481593394,485345150,481293132,481292888,481870872,481870754,481631719,481628925, 481631720,481628926,484386401,484325919,484279761,481364990,481364170,485108911,485109731,481365343,481364180,485110080,485108921, 485461600,485461588,486518688,483678244,486516141,483675633,481203710,484953644,481203457,484953899,481859623,481858668,485301624, 485301274,485178242,485178159,485341976,485341674,485114334,485114120,481667238,481667062,481896047,485566958,485566922,481896012, 485492025,485491374,481383898,481383891,485525133,485524916,485823685,485823675,482155703,482155693,481919222,481918827,485506045, 485504050,481919226,481918831,481314218,481314176,481372925,481372665,485118141,485117847,482155169,482155010,479090023,479089896, 486519248,483676465,483678822,486516954,481951298,481951203,481342632,481341537,485422816,485422759,485414857,485414807,485114339, 485114125,485114340,485114126,485559817,485559793,481590100,481589780,485038170,485038149,483345446,483345323,481898367,481898066, 485133366,485131992,481514080,481514019,485546660,481868409,481868588,485546483,481631724,481628931,481631725,481628932,485801708, 485801650,486519198,483678762,483676384,486516881,481795700,481795657,481511439,481511400,481954867,481954816,481750064,481750061, 481264623,481264439,483444426,483443970,485121840,485122295,483444424,485121838,485122293,483443968,485801199,485801114,482124023, 482123938,481758852,485465193,485465374,481758679,484985855,481234905,484417829,484349508,484301809,481630369,484369496,484270030, 484316209,484422263,484354647,484306241,485505104,484424634,484359856,484308602,484369483,484316196,484270017,484380924,484277629, 484323787,484394588,484331396,484285322,484443543,484409895,484339940,484293866,484443489,484384989,484324507,484278349,484387781, 484327215,484281141,484422937,484306905,484356645,484377357,484322251,484276093,485365130,481610521,484419539,484303519,484351460, 482097063,485776434,484408090,484338888,484292814,484377349,484322243,484276085,484411456,484295427,484341501,484444428,485491523, 484363227,484309940,484263761,484408140,484292864,484338938,484363949,484264483,484310662,484387017,484326451,484280377,484391296, 484283118,484329192,484363284,484263818,484309997,484363963,484264497,484310676,484391310,484283132,484329206,484419515,484303495, 484351436,485491585,485107518,484372437,484319150,484272992,484364435,484311148,484264969,484363383,484263917,484310096,484372838, 484319551,484273393,484419460,484303440,484351381,484364855,484265389,484311568,484362965,484309678,484263499,484387603,484327037, 484280963,484363999,484264533,484310712,484393931,484284665,484330739,484419551,484303531,484351472,484415488,484299468,484346502, 484387757,484327191,484281117,484375344,484321766,484275608,484394990,484285418,484331492,484413841,484343964,484297806,485505354, 484385059,484278419,484324577,484399421,484287836,484333910,484371169,484271724,484443706,484317882,484419549,484351470,484303529, 485420565,481670219,484414622,484298587,484344785,484364069,484310782,484264603,484415790,484299770,484346804,484392213,484283511, 484329585,484413882,484297847,484344005,484372903,484273458,484319616,484391273,484329169,484283095,484408100,484292824,484338898, 484414128,484298093,484344251,485505040,481808388,485487553,485001985,484364106,484310819,484264640,484411657,484341702,484295628, 484444228,484367730,484314443,484268264,484403066,484289851,484335925,484419597,484351518,484303577,484372506,484319219,484273061, 484422684,484356396,484306656,484422685,484356397,484306657,484419642,484351563,484303622,484419468,484303448,484351389,484364631, 484311344,484265165,481499866,484368214,484314927,484268748,484395063,484331565,484285491,484371188,484443905,484317901,484271743, 484413836,484343959,484297801,484364176,484264710,484310889,484362932,484263466,484309645,484363103,484309816,484263637,484423596, 484307564,484358673,485806313,485806320,484416902,484348176,484300882,484364579,484311292,484265113,484410263,484294234,484340308, 484363437,484310150,484263971,484363289,484263823,484310002,484409819,484339864,484293790,484408046,484338844,484292770,484422080, 484354466,484306060) ...省略500多个 ld.indipayser in子句... or ld.indipayser in (483435121,485491921,485491229,481690203,481689916,481520856,481520755,481898439,481898142,481430459, 481429068,485829180,485828858,483585375,483583501,481898271,481897956,481898395,481898095,481429876,481428052,481430365,481428936, 485622870,485622845,481953922,481953897,481342272,481341014,481342685,481341591,484412861,484296823,484342897,482228507,482226295, 482059057,485829120,485828783,484410980,484294951,484341025,485341942,485341626,482104613,482103959,476024026,476023392,479354610, 479355244,485506208,485504331,485506449,485504649,481630446,482161656,482161030,485414852,485414802,481407018,485139969,485140061, 481406926,481292994,481292723,481595191,475647095,478940615,481595203,475647108,478940628,481430099,481428372,485506248,485504393, 483585619,483583745,482202578,482201079,482013649,482013215,485133328,485131954,485524985,485524754,483585630,483583756,485008802, 485008781,481267747,481267768,485546704,481868233,481868633,485546310,481406992,481406892,485140035,485139935,481599032,481598665, 481406973,485139916,485140016,481406873,481459790,481459525,481815563,481815423,481459671,481459394,481742591,481742553,481363369, 481363213,483584338,483582447,481271582,481271283,485012358,485012661,481430472,481429081,485432563,481703015,481702793,485432341, 484938031,484937972,481576545,481576019,485104415,485104364,485506476,485504681,481659769,481659645,485506511,485504722,485837341, 485837308,481281837,481281135,485519385,485520085,484914974,484913321,484915019,484913409,475697671,475697595,472375125,467064510, 460778878,459403120,455152767,451292754,437811847,437811923,440579233,440579309,443323954,443324029,446196115,446196190,451292679, 408059771,412145899,412146020,416223862,416223945,419135025,419135109,421090466,421090550,424835994,424836077,428415307,428415384, 432137615,432137692,434943813,434943889,382221955,382222077,385744164,385744286,388759711,388759833,392484675,392484797,396668805, 396668889,399208174,399208296,401551264,401551386,405030115,405030236,408059650,455152690,459403043,460778801,467064434,469745874, 469745950,472375049,481823330,481823309,485061440,485060870,481293007,481292736,481791012,481790993,485055047,485054905,481264879, 481264601,485182951,485181901,482161443,482160773,484915392,484913132,481459595,481459310,485061296,485060685,481632400,481629790, 484367836,484268370,484314549,481965421,481964565,481333736,481333553,482062253,482055203,481342270,481341012,483444108,485121523, 483443652,485121978,481282606,485519758,481281510,485520852,482228644,482225455,481742602,481742564,485753875,485750411,483438857, 483435383,481386674,481386604,481459763,481459498,481238883,481238806,483585061,483583186,485465383,485465204,481459807,481459542, 485820222,482151739,485820213,482151748,481506957,481506888,485829083,485828735,481553536,481553461,485290131,485290208,480813211, 480811525,480809839,480802533,480803095,480804219,480804781,480805905,480806467,480807591,480808153,480809277,481195925,481196477, 480800847,480801409,480810963,480812649,475662583,475662548,472347913,472347948,469363482,469363447,481807284,481807269,479116742, 479116726,481157590,481157509,478569302,478569415,485505891,485503650,485797256,485797164,481925900,484370496,484271051,484317209, 475662579,475662559,472347924,469363458,469363478,472347944,485366698,485366602,481459761,481459496,485829059,485828711,482104644, 482104015,479355288,479354666,476023448,476024070,485061553,485061004,485061195,485060567,486518406,483677963,483675157,486515666, 481342717,481012977,481012976,481341623,481215908,478529522,485628896,481960727,485432467,485432171,481702919,481702623,485752755, 483437727,483434118,485749152,483345478,483345358,483345510,483345391,483345528,483345410,484400959,484334893,484288819,485506343, 485504525,481742595,481742557,484938040,484937944,485071415,485071319,481859721,481858934,485567428,485567290,483585457,483583583, 485133565,485132233,482161564,482160926,482104558,476024093,476023480,479354698,482103861,479355311,485829141,485828810,485190223, 481453746,481453533,485190010,485368352,485368337,481613708,481613723,481632457,481629847,481407010,485140053,481406918,485139961, 485829305,485828985,481856037,482097135,485776506,482096955,485776326,485831653,485831650,485492087,485491438,484945744,481408635, 481407982,481408804,481408266,482085026,482084946,481383961,481383927,481207531,484958546,484958564,481207513,481342353,481341097, 481815515,481815353,481519532,481519463,484945762,484945777,485824095,485824000,485116115,485116067,481370920,481370968,485829030, 485828770,481632418,481629808,485829281,485828960,485829088,485828740,482202678,482201289,485432590,481702496,485432044,481703042, 481753320,481753297,482104329,476023838,476023326,479354544,482104005,479355056,481467287,481467197,485506301,485504470,481430387, 481428981,484915992,484914171,485492004,485491346,484398623,484333459,484287385,484413427,484297389,484343463,484390123,484282715, 484328789,481365566,485110303,485109362,481364625,481576451,481575901,482017867,482017518) )
最终这个update子句使用500多个ld.indipayser in子句，这些in子句之间使用or进行连接，等于in子句最终的参数个数有40多万个。这就是udump目录中占用38.22G空间中消耗了34G空间的跟踪文件内容。
下面来查看cdump目录中的
[IBMP740-1:root:/oracle/admin/RLZY/cdump]#ls -lrt ...省略了大多数内容... drwxr-x--- 2 oracle dba 256 Oct 26 14:29 core_28377398 drwxr-x--- 2 oracle dba 256 Oct 26 14:52 core_6685364 drwxr-x--- 2 oracle dba 256 Oct 26 15:12 core_63635536 drwxr-x--- 2 oracle dba 256 Oct 26 15:35 core_17301998 drwxr-x--- 2 oracle dba 256 Oct 26 16:04 core_47317412 drwxr-x--- 2 oracle dba 256 Oct 26 16:29 core_7144314 drwxr-x--- 2 oracle dba 256 Oct 26 16:54 core_33030282 drwxr-x--- 2 oracle dba 256 Oct 26 17:29 core_27918436 drwxr-x--- 2 oracle dba 256 Oct 26 17:39 core_12321324 drwxr-x--- 2 oracle dba 256 Oct 26 17:49 core_45089220 drwxr-x--- 2 oracle dba 256 Oct 26 18:15 core_12517416 drwxr-x--- 2 oracle dba 256 Oct 26 18:35 core_3539788 drwxr-x--- 2 oracle dba 256 Oct 26 19:00 core_14549222 drwxr-x--- 2 oracle dba 256 Oct 26 19:26 core_60817918 drwxr-x--- 2 oracle dba 256 Oct 26 19:42 core_43647120 drwxr-x--- 2 oracle dba 256 Oct 26 19:52 core_13697930
查看最近生成的core_13697930目录中生成的core文件可以看到与/oracle/admin/RLZY/udump/rlzy_ora_13697930.trc文件中一样的SQL语句:
update lv_handle_mdiindiacc_detail ld set ld.push_flag= '1' where ( ld.indipayser in (485506300,485504469,481520527,481520481, 481203712,484953649,484953901,481203462,481382692,481382673,485061110,485060889,481631327,481628371,481714509,481714502,485422790, 485422743,485505625,485504046,485505138,485422775,483924474,485422767,481537563,481537508,485422795,485422751,485710571,482042800, 482042766,485710537,485233344,485233182,485233274,481470443,481469597,485422785,485422737,481352719,481352131,485422780,485422731, 486517983,483676463,483677515,486516952,486517971,486516920,483676426,483677504,486517984,483676467,483677516,486516956,485834372, 485834335,486517621,483677148,486515894,483675382,482155166,482155007,485341981,485341686,485148741,485148592,485233418,485233219, 485691049,485690926,482024499,482024622,481430441,481429048,485422809,485422738,485003015,485002902,481365287,485108845,485110024, 481364104,481576480,481575936,485345250,481593493,481593394,485345150,481293132,481292888,481870872,481870754,481631719,481628925, 481631720,481628926,484386401,484325919,484279761,481364990,481364170,485108911,485109731,481365343,481364180,485110080,485108921, 485461600,485461588,486518688,483678244,486516141,483675633,481203710,484953644,481203457,484953899,481859623,481858668,485301624, 485301274,485178242,485178159,485341976,485341674,485114334,485114120,481667238,481667062,481896047,485566958,485566922,481896012, 485492025,485491374,481383898,481383891,485525133,485524916,485823685,485823675,482155703,482155693,481919222,481918827,485506045, 485504050,481919226,481918831,481314218,481314176,481372925,481372665,485118141,485117847,482155169,482155010,479090023,479089896, 486519248,483676465,483678822,486516954,481951298,481951203,481342632,481341537,485422816,485422759,485414857,485414807,485114339, 485114125,485114340,485114126,485559817,485559793,481590100,481589780,485038170,485038149,483345446,483345323,481898367,481898066, 485133366,485131992,481514080,481514019,485546660,481868409,481868588,485546483,481631724,481628931,481631725,481628932,485801708, 485801650,486519198,483678762,483676384,486516881,481795700,481795657,481511439,481511400,481954867,481954816,481750064,481750061, 481264623,481264439,483444426,483443970,485121840,485122295,483444424,485121838,485122293,483443968,485801199,485801114,482124023, 482123938,481758852,485465193,485465374,481758679,484985855,481234905,484417829,484349508,484301809,481630369,484369496,484270030, 484316209,484422263,484354647,484306241,485505104,484424634,484359856,484308602,484369483,484316196,484270017,484380924,484277629, 484323787,484394588,484331396,484285322,484443543,484409895,484339940,484293866,484443489,484384989,484324507,484278349,484387781, 484327215,484281141,484422937,484306905,484356645,484377357,484322251,484276093,485365130,481610521,484419539,484303519,484351460, 482097063,485776434,484408090,484338888,484292814,484377349,484322243,484276085,484411456,484295427,484341501,484444428,485491523, 484363227,484309940,484263761,484408140,484292864,484338938,484363949,484264483,484310662,484387017,484326451,484280377,484391296, 484283118,484329192,484363284,484263818,484309997,484363963,484264497,484310676,484391310,484283132,484329206,484419515,484303495, 484351436,485491585,485107518,484372437,484319150,484272992,484364435,484311148,484264969,484363383,484263917,484310096,484372838, 484319551,484273393,484419460,484303440,484351381,484364855,484265389,484311568,484362965,484309678,484263499,484387603,484327037, 484280963,484363999,484264533,484310712,484393931,484284665,484330739,484419551,484303531,484351472,484415488,484299468,484346502, 484387757,484327191,484281117,484375344,484321766,484275608,484394990,484285418,484331492,484413841,484343964,484297806,485505354, 484385059,484278419,484324577,484399421,484287836,484333910,484371169,484271724,484443706,484317882,484419549,484351470,484303529, 485420565,481670219,484414622,484298587,484344785,484364069,484310782,484264603,484415790,484299770,484346804,484392213,484283511, 484329585,484413882,484297847,484344005,484372903,484273458,484319616,484391273,484329169,484283095,484408100,484292824,484338898, 484414128,484298093,484344251,485505040,481808388,485487553,485001985,484364106,484310819,484264640,484411657,484341702,484295628, 484444228,484367730,484314443,484268264,484403066,484289851,484335925,484419597,484351518,484303577,484372506,484319219,484273061, 484422684,484356396,484306656,484422685,484356397,484306657,484419642,484351563,484303622,484419468,484303448,484351389,484364631, 484311344,484265165,481499866,484368214,484314927,484268748,484395063,484331565,484285491,484371188,484443905,484317901,484271743, 484413836,484343959,484297801,484364176,484264710,484310889,484362932,484263466,484309645,484363103,484309816,484263637,484423596, 484307564,484358673,485806313,485806320,484416902,484348176,484300882,484364579,484311292,484265113,484410263,484294234,484340308, 484363437,484310150,484263971,484363289,484263823,484310002,484409819,484339864,484293790,484408046,484338844,484292770,484422080, 484354466,484306060) ...省略500多个 ld.indipayser in子句... or ld.indipayser in (483435121,485491921,485491229,481690203,481689916,481520856,481520755,481898439,481898142,481430459, 481429068,485829180,485828858,483585375,483583501,481898271,481897956,481898395,481898095,481429876,481428052,481430365,481428936, 485622870,485622845,481953922,481953897,481342272,481341014,481342685,481341591,484412861,484296823,484342897,482228507,482226295, 482059057,485829120,485828783,484410980,484294951,484341025,485341942,485341626,482104613,482103959,476024026,476023392,479354610, 479355244,485506208,485504331,485506449,485504649,481630446,482161656,482161030,485414852,485414802,481407018,485139969,485140061, 481406926,481292994,481292723,481595191,475647095,478940615,481595203,475647108,478940628,481430099,481428372,485506248,485504393, 483585619,483583745,482202578,482201079,482013649,482013215,485133328,485131954,485524985,485524754,483585630,483583756,485008802, 485008781,481267747,481267768,485546704,481868233,481868633,485546310,481406992,481406892,485140035,485139935,481599032,481598665, 481406973,485139916,485140016,481406873,481459790,481459525,481815563,481815423,481459671,481459394,481742591,481742553,481363369, 481363213,483584338,483582447,481271582,481271283,485012358,485012661,481430472,481429081,485432563,481703015,481702793,485432341, 484938031,484937972,481576545,481576019,485104415,485104364,485506476,485504681,481659769,481659645,485506511,485504722,485837341, 485837308,481281837,481281135,485519385,485520085,484914974,484913321,484915019,484913409,475697671,475697595,472375125,467064510, 460778878,459403120,455152767,451292754,437811847,437811923,440579233,440579309,443323954,443324029,446196115,446196190,451292679, 408059771,412145899,412146020,416223862,416223945,419135025,419135109,421090466,421090550,424835994,424836077,428415307,428415384, 432137615,432137692,434943813,434943889,382221955,382222077,385744164,385744286,388759711,388759833,392484675,392484797,396668805, 396668889,399208174,399208296,401551264,401551386,405030115,405030236,408059650,455152690,459403043,460778801,467064434,469745874, 469745950,472375049,481823330,481823309,485061440,485060870,481293007,481292736,481791012,481790993,485055047,485054905,481264879, 481264601,485182951,485181901,482161443,482160773,484915392,484913132,481459595,481459310,485061296,485060685,481632400,481629790, 484367836,484268370,484314549,481965421,481964565,481333736,481333553,482062253,482055203,481342270,481341012,483444108,485121523, 483443652,485121978,481282606,485519758,481281510,485520852,482228644,482225455,481742602,481742564,485753875,485750411,483438857, 483435383,481386674,481386604,481459763,481459498,481238883,481238806,483585061,483583186,485465383,485465204,481459807,481459542, 485820222,482151739,485820213,482151748,481506957,481506888,485829083,485828735,481553536,481553461,485290131,485290208,480813211, 480811525,480809839,480802533,480803095,480804219,480804781,480805905,480806467,480807591,480808153,480809277,481195925,481196477, 480800847,480801409,480810963,480812649,475662583,475662548,472347913,472347948,469363482,469363447,481807284,481807269,479116742, 479116726,481157590,481157509,478569302,478569415,485505891,485503650,485797256,485797164,481925900,484370496,484271051,484317209, 475662579,475662559,472347924,469363458,469363478,472347944,485366698,485366602,481459761,481459496,485829059,485828711,482104644, 482104015,479355288,479354666,476023448,476024070,485061553,485061004,485061195,485060567,486518406,483677963,483675157,486515666, 481342717,481012977,481012976,481341623,481215908,478529522,485628896,481960727,485432467,485432171,481702919,481702623,485752755, 483437727,483434118,485749152,483345478,483345358,483345510,483345391,483345528,483345410,484400959,484334893,484288819,485506343, 485504525,481742595,481742557,484938040,484937944,485071415,485071319,481859721,481858934,485567428,485567290,483585457,483583583, 485133565,485132233,482161564,482160926,482104558,476024093,476023480,479354698,482103861,479355311,485829141,485828810,485190223, 481453746,481453533,485190010,485368352,485368337,481613708,481613723,481632457,481629847,481407010,485140053,481406918,485139961, 485829305,485828985,481856037,482097135,485776506,482096955,485776326,485831653,485831650,485492087,485491438,484945744,481408635, 481407982,481408804,481408266,482085026,482084946,481383961,481383927,481207531,484958546,484958564,481207513,481342353,481341097, 481815515,481815353,481519532,481519463,484945762,484945777,485824095,485824000,485116115,485116067,481370920,481370968,485829030, 485828770,481632418,481629808,485829281,485828960,485829088,485828740,482202678,482201289,485432590,481702496,485432044,481703042, 481753320,481753297,482104329,476023838,476023326,479354544,482104005,479355056,481467287,481467197,485506301,485504470,481430387, 481428981,484915992,484914171,485492004,485491346,484398623,484333459,484287385,484413427,484297389,484343463,484390123,484282715, 484328789,481365566,485110303,485109362,481364625,481576451,481575901,482017867,482017518) )
所以应该是该语句中的in子句的硬编码值过多引起的，in子句硬编码值有40多万个。这样的语句解析将消耗的很多的shared_pool中的空间，从而导致系统异常。
处理方法:
应用改写语句，将in子句中的这些值存放到某个临时表中，将使用临时表与要被更新的表进行关联。