前不久从一个数据库执行导出操作时报错,通过直接路径方式跳过后,导入时候再次报错。推测是由于源数据库出现的异常导致表中数据超过表定义的精度。
由于源数据库中错误记录已经被删除,因此只能想办法从导出的dmp文件中获取错误的记录。
导出、导入过程的描述可以参考:
EXP在9R2上导出时报错ORA-3113和ORA-24324:http://yangtingkun.itpub.net/post/468/460647
EXP在9R2上导出时报错ORA-3113和ORA-24324(二):http://yangtingkun.itpub.net/post/468/460831
继续探讨导致SQLPLUS程序崩溃的原因。
获取导致导入失败的数据:http://yangtingkun.itpub.net/post/468/461401
获取导致导入失败的数据(二):http://yangtingkun.itpub.net/post/468/461600
获取导致导入失败的数据(三):http://yangtingkun.itpub.net/post/468/461660
BEFORE触发器修正数据错误:http://yangtingkun.itpub.net/post/468/461506
在上面一篇文章中,通过修改表结构和设置触发器,最终使得异常数据成功插入。不过在查询异常数据时,导致了sqlplus程序的崩溃:
SQL> SELECT * FROM
2 (
3 SELECT ROWNUM RN, TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
4 UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
5 FROM SHGOV_ORDER
6 WHERE ROWNUM < 31324
7 )
8 WHERE RN > 31322;
RN TRADE_RATE ORDER_AMOUONT SEND_AMOUNT RECEIVE_AMOUNT UNIT_PRICE MAX_PRICE MIN_PRICE PRICE_RATE
---------- ---------- ------------- ----------- -------------- ---------- ---------- ---------- ----------
31323 -5.353E+19 -3.120E+77 -5.054E+29 -5.300E+35 -1.952E-15 3.3295E-31 -~ -5.156E+23
*** glibc detected *** sqlplus: free(): invalid next size (normal): 0x000000001ca94a40 ***
======= Backtrace: =========
/lib64/libc.so.6[0x3c0a66e8a0]
/lib64/libc.so.6(cfree+0x8c)[0x3c0a671fbc]
sqlplus(safifre+0xc)[0x42c69c]
sqlplus[0x423675]
sqlplus[0x4155fa]
sqlplus[0x413a7b]
sqlplus[0x419bba]
sqlplus[0x433596]
sqlplus[0x43253b]
sqlplus[0x408433]
sqlplus[0x40780e]
/lib64/libc.so.6(__libc_start_main+0xf4)[0x3c0a61d8a4]
sqlplus[0x40773a]
======= Memory map: ========
00400000-00494000 r-xp 00000000 08:02 10031306 /opt/oracle/product/9.2/bin/sqlplus
00594000-0059b000 rwxp 00094000 08:02 10031306 /opt/oracle/product/9.2/bin/sqlplus
1ca33000-1cb16000 rwxp 1ca33000 00:00 0
3c0a200000-3c0a21a000 r-xp 00000000 08:02 3365501 /lib64/ld-2.5.so
3c0a419000-3c0a41a000 r-xp 00019000 08:02 3365501 /lib64/ld-2.5.so
3c0a41a000-3c0a41b000 rwxp 0001a000 08:02 3365501 /lib64/ld-2.5.so
3c0a600000-3c0a744000 r-xp 00000000 08:02 3365502 /lib64/libc-2.5.so
3c0a744000-3c0a944000 ---p 00144000 08:02 3365502 /lib64/libc-2.5.so
3c0a944000-3c0a948000 r-xp 00144000 08:02 3365502 /lib64/libc-2.5.so
3c0a948000-3c0a949000 rwxp 00148000 08:02 3365502 /lib64/libc-2.5.so
3c0a949000-3c0a94e000 rwxp 3c0a949000 00:00 0
3c0aa00000-3c0aa82000 r-xp 00000000 08:02 3365504 /lib64/libm-2.5.so
3c0aa82000-3c0ac81000 ---p 00082000 08:02 3365504 /lib64/libm-2.5.so
3c0ac81000-3c0ac82000 r-xp 00081000 08:02 3365504 /lib64/libm-2.5.so
3c0ac82000-3c0ac83000 rwxp 00082000 08:02 3365504 /lib64/libm-2.5.so
3c0ae00000-3c0ae02000 r-xp 00000000 08:02 3365505 /lib64/libdl-2.5.so
3c0ae02000-3c0b002000 ---p 00002000 08:02 3365505 /lib64/libdl-2.5.so
3c0b002000-3c0b003000 r-xp 00002000 08:02 3365505 /lib64/libdl-2.5.so
3c0b003000-3c0b004000 rwxp 00003000 08:02 3365505 /lib64/libdl-2.5.so
3c0b200000-3c0b215000 r-xp 00000000 08:02 3365398 /lib64/libpthread-2.5.so
3c0b215000-3c0b414000 ---p 00015000 08:02 3365398 /lib64/libpthread-2.5.so
3c0b414000-3c0b415000 r-xp 00014000 08:02 3365398 /lib64/libpthread-2.5.so
3c0b415000-3c0b416000 rwxp 00015000 08:02 3365398 /lib64/libpthread-2.5.so
3c0b416000-3c0b41a000 rwxp 3c0b416000 00:00 0
3c0ca00000-3c0ca0d000 r-xp 00000000 08:02 3365507 /lib64/libgcc_s-4.1.1-20070105.so.1
3c0ca0d000-3c0cc0c000 ---p 0000d000 08:02 3365507 /lib64/libgcc_s-4.1.1-20070105.so.1
3c0cc0c000-3c0cc0d000 rwxp 0000c000 08:02 3365507 /lib64/libgcc_s-4.1.1-20070105.so.1
3c11a00000-3c11a14000 r-xp 00000000 08:02 3365523 /lib64/libnsl-2.5.so
3c11a14000-3c11c13000 ---p 00014000 08:02 3365523 /lib64/libnsl-2.5.so
3c11c13000-3c11c14000 r-xp 00013000 08:02 3365523 /lib64/libnsl-2.5.so
3c11c14000-3c11c15000 rwxp 00014000 08:02 3365523 /lib64/libnsl-2.5.so
3c11c15000-3c11c17000 rwxp 3c11c15000 00:00 0
2aaaaaaab000-2aaaaaaad000 rwxp 2aaaaaaab000 00:00 0
2aaaaaaad000-2aaaab5fd000 r-xp 00000000 08:02 10162165 /opt/oracle/product/9.2/lib/libclntsh.so.9.0
2aaaab5fd000-2aaaab6fd000 ---p 00b50000 08:02 10162165 /opt/oracle/product/9.2/lib/libclntsh.so.9.0
2aaaab6fd000-2aaaab75c000 rwxp 00b50000 08:02 10162165 /opt/oracle/product/9.2/lib/libclntsh.so.9.0
2aaaab75c000-2aaaab76f000 rwxp 2aaaab75c000 00:00 0
2aaaab76f000-2aaaab771000 r-xp 00000000 08:02 10162044 /opt/oracle/product/9.2/lib/libwtc9.so
2aaaab771000-2aaaab870000 ---p 00002000 08:02 10162044 /opt/oracle/product/9.2/lib/libwtc9.so
2aaaab870000-2aaaab871000 rwxp 00001000 08:02 10162044 /opt/oracle/product/9.2/lib/libwtc9.so
2aaaab871000-2aaaab894000 rwxp 2aaaab871000 00:00 0
2aaaab895000-2aaaab8bf000 rwxp 2aaaab895000 00:00 0
2aaaab8e2000-2aaaab8ec000 r-xp 00000000 08:02 3365244 /lib64/libnss_files-2.5.so
2aaaab8ec000-2aaaabaeb000 ---p 0000a000 08:02 3365244 /lib64/libnss_files-2.5.so
2aaaabaeb000-2aaaabaec000 r-xp 00009000 08:02 3365244 /lib64/libnss_files-2.5.so
2aaaabaec000-2aaaabaed000 rwxp 0000a000 08:02 3365244 /lib64/libnss_files-2.5.so
2aaaac000000-2aaaac021000 rwxp 2aaaac000000 00:00 0
2aaaac021000-2aaab0000000 ---p 2aaaac021000 00:00 0
7fff2465d000-7fff24673000 rwxp 7fff2465d000 00:00 0 [stack]
ffffffffff600000-ffffffffffe00000 ---p 00000000 00:00 0 [vdso]
Aborted
显然导致错误的原因是由于数据的异常,导致了Oracle在处理数据的时候,出现了内存冲突的问题,最终导致sqlplus程序的结束。参照上一篇文章,如果将这些异常的“数值”转化为字符串,并安装字符串类型来访问,并不会造成问题,而如果直接根据数值类型来访问这些异常的“数值”,就导致了问题的产生。
不过,如果因为数据异常就导致如此严重的程序崩溃,是否Oracle也太不健壮了,如果sqlplus给出错误信息,似乎比直接程序崩溃更合理一些。
根据这个信息查询metalink,发现了部分类似的情况,Oracle认为这并非是个bug,而是由于没有设置LD_ASSUME_KERNEL参数导致的。类似的描述可以参考:Doc ID: Note:352493.1。
不过尝试了设置LD_ASSUME_KERNEL,发现这个方法似乎没有什么效果,依然造成了sqlplus程序的崩溃。
尝试了不同平台上的SQLPLUS客户端,发现表现也不相同。
以10.2.0.3版本为例,linux 64-X86平台上的错误和上面的一致。
而Solaris Sparc64则出现了下面的错误:
$ sqlplus test/test@172.25.198.230/bjtest
SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 3月 19 23:01:53 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> SELECT * FROM
2 (
3 SELECT ROWNUM RN, TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
4 UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
5 FROM SHGOV_ORDER
6 WHERE ROWNUM < 31324
7 )
8 WHERE RN > 31322;
Segmentation Fault - core dumped
而对于Windows平台下的32为sqlplus,似乎并没有引起任何的异常:
SQL> conn test/test@172.25.198.230/bjtest
已连接。
SQL> SELECT * FROM
2 (
3 SELECT ROWNUM RN, TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
4 UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
5 FROM SHGOV_ORDER
6 WHERE ROWNUM < 31324
7 )
8 WHERE RN > 31322;
RN TRADE_RATE ORDER_AMOUONT SEND_AMOUNT RECEIVE_AMOUNT UNIT_PRICE MAX_PRICE MIN_PRICE PRICE_RATE
---------- ---------- ------------- ----------- -------------- ---------- ---------- ---------- ----
31323 -5.353E+19 -3.120E+77 -5.054E+29 -5.300E+35 -1.952E-15 3.3295E-31 -~ -5.156E+2
而且无论是windows下的sqlplusw工具,还是命令行sqlplus命令,都不会报错。看来似乎sqlplus客户端的保护代码还是windows环境下更好一些。
再次查询metalink,发现如果设置环境变量MALLOC_CHECK_=1,则sqlplus仍然会报错,但是不会导致程序的崩溃。
[oracle@bjtest ~]$ export MALLOC_CHECK_=1
[oracle@bjtest ~]$ sqlplus test/test
malloc: using debugging hooks
SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 5月 19 23:17:20 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
malloc: using debugging hooks
连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> SET PAGES 100 LINES 120
SQL> SELECT * FROM
2 (
3 SELECT ROWNUM RN, TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
4 UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
5 FROM SHGOV_ORDER
6 WHERE ROWNUM < 31324
7 )
8 WHERE RN > 31322;
RN TRADE_RATE ORDER_AMOUONT SEND_AMOUNT RECEIVE_AMOUNT UNIT_PRICE MAX_PRICE MIN_PRICE PRICE_RATE
---------- ---------- ------------- ----------- -------------- ---------- ---------- ---------- ----------
31323 -5.353E+19 -3.120E+77 -5.054E+29 -5.300E+35 -1.952E-15 3.3295E-31 -~ -5.156E+23
*** glibc detected *** sqlplus: free(): invalid next size (normal): 0x000000000457fd70 ***
*** glibc detected *** sqlplus: free(): invalid pointer: 0x000000000457fdd0 ***
SQL> SELECT * FROM DUAL;
D
-
X
更多的相关描述可以参考:Bug No. 6074363。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-277828/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-277828/