获取导致导入失败的数据（三）
前不久从一个数据库执行导出操作时报错，通过直接路径方式跳过后，导入时候再次报错。推测是由于源数据库出现的异常导致表中数据超过表定义的精度。
由于源数据库中错误记录已经被删除，因此只能想办法从导出的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
继续寻找解决问题的方法，并最终获取了导致错误的记录。
获取导致导入失败的数据：http://yangtingkun.itpub.net/post/468/461401
获取导致导入失败的数据（二）：http://yangtingkun.itpub.net/post/468/461600
BEFORE触发器修正数据错误：http://yangtingkun.itpub.net/post/468/461506
在上一篇中，发现采用系统触发器的方法只能获取导入操作的语句，也就是说，获取的SQL是所有问题的起源SQL，而并非导致操作的SQL语句。
也就是说，利用触发器获取错误信息的方式是行不通的。既然触发器中引发错误的SQL无法被系统触发器所捕获，那么这个SQL能不能被Oracle的V$SQL所捕获呢。
先做个测试，看看这种由于精度问题导致的SQL语句是否可以从V$SQL中查询到：
SQL> CREATE TABLE TEST (ID NUMBER(3));
表已创建。
SQL> INSERT INTO TEST VALUES (100);
已创建 1 行。
SQL> INSERT INTO TEST VALUES (1000);
INSERT INTO TEST VALUES (1000)
*
ERROR 位于第 1 行:
ORA-01438: 值大于此列指定的允许精确度
SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'INSERT INTO TEST VALUES%';
SQL_TEXT
-----------------------------------------------------------------------------
INSERT INTO TEST VALUES (1000)
INSERT INTO TEST VALUES (100)
从上面的结果可以看到，由于精度问题出现的错误，是会被Oracle的V$SQL所记录下来的，但是如果分析阶段报错，就无法从V$SQL中查询了：
SQL> SELECT * FROM ASDFA;
SELECT * FROM ASDFA
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在
SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%ASDFA%';
SQL_TEXT
---------------------------------------------------------
SELECT SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%ASDFA%'
而根据上面最后一次的导入报错信息看，似乎获取的数值格式错误，出现了逗号，导致触发器中SQL语句分析时报错，而这种方式是无法被V$SQL所捕获的。
下面修改SHGOV_ORDER_BAK表，将字段改为VARCHAR2(4000)，并修改触发器，使之按照字符类型插入数据：
SQL> DROP TABLE SHGOV_ORDER_BAK;
表已丢弃。
SQL> CREATE TABLE SHGOV_ORDER_BAK
2 (
3 TRADE_RATE VARCHAR2(4000),
4 ORDER_AMOUONT VARCHAR2(4000),
5 SEND_AMOUNT VARCHAR2(4000),
6 RECEIVE_AMOUNT VARCHAR2(4000),
7 UNIT_PRICE VARCHAR2(4000),
8 MAX_PRICE VARCHAR2(4000),
9 MIN_PRICE VARCHAR2(4000),
10 PRICE_RATE VARCHAR2(4000)
11 );
表已创建。
SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQL
2 BEFORE INSERT ON SHGOV_ORDER
3 FOR EACH ROW
4 DECLARE
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 EXECUTE IMMEDIATE 'INSERT INTO SHGOV_ORDER_BAK
8 (
9 TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
10 UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
11 )
12 VALUES
13 (
14 ''' || :NEW.TRADE_RATE || ''', ''' || :NEW.ORDER_AMOUONT || ''', '''
15 || :NEW.SEND_AMOUNT || ''', ''' || :NEW.RECEIVE_AMOUNT || ''', '''
16 || :NEW.UNIT_PRICE || ''', ''' || :NEW.MAX_PRICE || ''', '''
17 || :NEW.MIN_PRICE || ''', ''' || :NEW.PRICE_RATE || ''')';
18 COMMIT;
19 END;
20 /
触发器已创建
下面再次执行导致操作，准备从V$SQL中获取异常的数据：
[oracle@bjtest data]$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y
Import: Release 9.2.0.4.0 - Production on 星期五 5月 9 21:29:00 2008
Copyright (c) 1982, 2002, Oracle Corporation. 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
经由常规路径导出由EXPORT:V09.02.00创建的文件
警告: 此对象由 SHGOV 导出, 而不是当前用户
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将SHGOV的对象导入到 TEST
. . 正在导入表 "SHGOV_ORDER" 420835行被导入
IMP-00009: 导出文件异常结束
成功终止导入，但出现警告。
另人吃惊的是，导入居然成功了。要知道虽然SHGOV_ORDER_BAK表的数值类型字段全部设置为VARCHAR2(4000)，但是SHGOV_ORDER表中仍然为NUMBER类型。
在触发器中直接转化为字符类型会报错，无效的数值，这一点可以参考第一篇文章中的例子，但是Oracle的imp居然成功的将输入插入进去，真是不可思议。
SQL> SELECT COUNT(*) FROM SHGOV_ORDER_BAK;
COUNT(*)
----------
420835
SQL> SELECT COUNT(*) FROM SHGOV_ORDER;
COUNT(*)
----------
420835
SQL> DESC SHGOV_ORDER
名称 是否为空? 类型
------------------------------------ -------- --------------
ORDER_ITEM_ID NOT NULL CHAR(24)
PLAT_ID CHAR(24)
PRODUCT_ID CHAR(24)
CODE VARCHAR2(50)
C_NAME_CHN VARCHAR2(300)
MEDICAL_ID CHAR(24)
MEDICAL_CODE VARCHAR2(150)
TRADE_NAME VARCHAR2(300)
USE_UNIT VARCHAR2(150)
USED_NAME VARCHAR2(1000)
MANUFACTURE_ID CHAR(24)
MANUFACTURE_NAME VARCHAR2(150)
MANUFACTURE_ABBR VARCHAR2(150)
M_SPELL_ABBR VARCHAR2(150)
STAND_RATE VARCHAR2(150)
SPEC VARCHAR2(4000)
E_NAME_CHN VARCHAR2(150)
JX_NAME_CHN VARCHAR2(150)
WRAP_NAME VARCHAR2(150)
TAX_PRICE VARCHAR2(150)
NATIONAL_RETAIL_PRICE VARCHAR2(150)
TRADE_RATE NUMBER
MED_INSURE VARCHAR2(100)
ORDER_ID CHAR(24)
ORDER_AMOUONT NUMBER
SEND_AMOUNT NUMBER
RECEIVE_AMOUNT NUMBER
UNIT_PRICE NUMBER
SOURCE_TYPE CHAR(1)
MAX_PRICE NUMBER
HIS_NAME VARCHAR2(150)
HIS_ABBR VARCHAR2(50)
HIS_ID CHAR(24)
DEALER_NAME VARCHAR2(150)
DEALER_ID CHAR(24)
DEALER_ABBR VARCHAR2(50)
MIN_PRICE NUMBER
SENDER_NAME VARCHAR2(150)
SENDER_ID CHAR(24)
SENDER_ABBR VARCHAR2(150)
CREATE_DATE DATE
SENDE_DATE DATE
PRICE_RATE NUMBER
不管怎么说，现在数据已经导入了，检查一下第31323条记录的情况：
SQL> SELECT * FROM (SELECT ROWNUM RN, A.* FROM SHGOV_ORDER_BAK A WHERE ROWNUM < 31324)
2 WHERE RN > 31322;
RN
----------
TRADE_RATE
-------------------------------------------------------------------------------
ORDER_AMOUONT
-------------------------------------------------------------------------------
SEND_AMOUNT
-------------------------------------------------------------------------------
RECEIVE_AMOUNT
-------------------------------------------------------------------------------
UNIT_PRICE
-------------------------------------------------------------------------------
MAX_PRICE
-------------------------------------------------------------------------------
MIN_PRICE
-------------------------------------------------------------------------------
PRICE_RATE
-------------------------------------------------------------------------------
31323
-53525351485153525352.535299ED98~m~m77311951
-311951531753535353535349535353535353535345000000000000000000000000000000000000
-505353535353535252524948536449.535152534552
-000000000000000000000000000000000000
-.00000000000000195153175353535353535253535353535352505049
.00000000000000000000000000000033293311.,
-~
-515550504648000000000000
果然，数值类型已经变为完全没有意义的乱码了，里面甚至还包括一些字母和标点符号。最后看看Oracle的imp导入进去的数值类型是什么样子：
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): 0x000000000cda7520 ***
======= 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
0cd25000-0ce8c000 rwxp 0cd25000 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
7fffe2300000-7fffe2316000 rwxp 7fffe2300000 00:00 0 [stack]
ffffffffff600000-ffffffffffe00000 ---p 00000000 00:00 0 [vdso]
Aborted
没想到，一个简单的查询居然导致sqlplus直接错误退出。
检查alert文件，没有发现对应错误信息的生成。看来这种逻辑错误造成的数据异常还是有很大危害性的，可能会直接造成应用程序的错误。
像上面这个错误似乎是由于错误数据导致sqlplus在调用free函数的时候出现了地址错误，从直接被Oracle强制结束。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-264934/ ，如需转载，请注明出处，否则将追究法律责任。
