我申请这个blog是为了督促自己，把自己平时的一些想法和思考结果保留下来。 本博客所有内容均为原创，如有转载请注明作者和出处

获取导致导入失败的数据

上一篇 / 下一篇  2008-05-07 21:38:10 / 个人分类：ORACLE

查看( 56 ) / 评论( 0 ) / 评分( 0 / 0 )

前不久从一个数据库执行导出操作时报错，通过直接路径方式跳过后，导入时候再次报错。推测是由于源数据库出现的异常导致表中数据超过表定义的精度。

由于源数据库中错误记录已经被删除，因此只能想办法从导出的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

 

 

导入时报错如下：

$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y

Import: Release 9.2.0.4.0 - Production on星期三5月7 23:12:20 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


J?2QuULi*q0连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionITPUB个人空间.|5D$h&`H{
With the Partitioning, OLAP and Oracle Data Mining options
FDN$_P,C%no~:U0JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件

警告:此对象由SHGOV导出,而不是当前用户

已经完成ZHS16GBK字符集和AL16UTF16 NCHAR字符集中的导入ITPUB个人空间3py(q;\(z0d
.正在将SHGOV的对象导入到TEST
SxR*tL9n[.fS0. .正在导入表                   "SHGOV_ORDER"ITPUB个人空间C"K2G{ Z+E S K
IMP-00058:遇到ORACLE错误1438ITPUB个人空间!ChiJ'L+T'[d tc
ORA-01438:值大于此列指定的允许精确度
g*ZA.eh Feq(~0IMP-00028:上一个表的部分导入已回退:回退31322行ITPUB个人空间9\9g Wk A`$upO
IMP-00009:导出文件异常结束ITPUB个人空间_}Hz$e h#}:z7b
成功终止导入，但出现警告。

根据这个错误，是无法判断问题到底出现在哪一列上，也看不到导致错误数据。不过由于导出过程中出现了1438错误，因此首先相当的方法是利用系统错误触发器获取插入失败记录的SQL语句。

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on星期三5月7 22:54:26 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


M$u\tJ]-Wxz lA0{0连接到:
fx)x9~i0Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionITPUB个人空间j Ul#bV&x\t,j_
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间E2[Nw0p'\;g X
JServer Release 9.2.0.4.0 - Production

SQL> CREATE TABLE T_LOG (EXECUTE_DATE DATE, SQL_STATMENT CLOB);

表已创建。

SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQL AFTER SERVERERROR ON DATABASE
i6?2CG*kP f0  2  DECLARE
!Ug;|;wQR5T0  3     V_SQL_OUT ORA_NAME_LIST_T;ITPUB个人空间D!v,?"kUN7|&uE
  4     V_NUM NUMBER;
6N"BIB`eh0  5     V_SQL_STATMENT VARCHAR2(32767);
] }&b3P9Ad+n7vv0  6  BEGIN
c s8n,cN#R6f0  7     IF IS_SERVERERROR(1438) THENITPUB个人空间!U#l!Y9\t(L^n
  8             V_NUM := ORA_SQL_TXT(V_SQL_OUT);
.y_"ZtR2X-DetBR0  9             FOR I IN 1 .. V_NUM LOOP
EZ/TH*QOq+i]0 10                     V_SQL_STATMENT := V_SQL_STATMENT || V_SQL_OUT(I);
&L ]r\.A0 11             ENDLOOP;ITPUB个人空间,~J^XMA2h.N~
 12             INSERT INTO T_LOG (EXECUTE_DATE, SQL_STATMENT)ITPUB个人空间pO`i H
 13             VALUES (SYSDATE, V_SQL_STATMENT);ITPUB个人空间1VlTP'?0`
 14     END IF;ITPUB个人空间RO.fc|ciy$D^
 15  END;
#qD ncC4OX N0Es`0 16  /

触发器已创建

再次运行导入命令后，查询T_LOG表中的记录：

SQL> SET LONG 10000ITPUB个人空间Ja)ECt0t a-z?
SQL> SELECT SQL_STATMENT FROM T_LOG;

SQL_STATMENT
/_/D?y%|'a P0------------------------------------------------------------------------------ITPUB个人空间4HP;PdjH
INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "SHGOV_ORDER" ("ORDER_ITEM_ID", "PLAT_IDITPUB个人空间 Ht"{4eG?\6D[{$i
", "PRODUCT_ID", "CODE", "C_NAME_CHN", "MEDICAL_ID", "MEDICAL_CODE", "TRADE_NAME
M9{"L1I'[0", "USE_UNIT", "USED_NAME", "MANUFACTURE_ID", "MANUFACTURE_NAME", "MANUFACTURE_AITPUB个人空间@;D?{.@h I6TRw%p
BBR", "M_SPELL_ABBR", "STAND_RATE", "SPEC", "E_NAME_CHN", "JX_NAME_CHN", "WRAP_NITPUB个人空间i)Vi,R8fW}6]
AME", "TAX_PRICE", "NATIONAL_RETAIL_PRICE", "TRADE_RATE", "MED_INSURE", "ORDER_IITPUB个人空间}kG L%T"NJJq |
D", "ORDER_AMOUONT", "SEND_AMOUNT", "RECEIVE_AMOUNT", "UNIT_PRICE", "SOURCE_TYPE
D4OS ^8F)uf0", "MAX_PRICE", "HIS_NAME", "HIS_ABBR", "HIS_ID", "DEALER_NAME", "DEALER_ID", "DITPUB个人空间mGSK&t$N'S*gm
EALER_ABBR", "MIN_PRICE", "SENDER_NAME", "SENDER_ID", "SENDER_ABBR", "CREATE_DAT
O:_[&b kd/R0E", "SENDE_DATE", "PRICE_RATE") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10,ITPUB个人空间]0nN fVdSY
 :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26,ITPUB个人空间O~ fA#t0v&]EDnB
 :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42,ITPUB个人空间)p}mq!Z_y
 :43)

Oracle导入过程使用了绑定变量，显然这种方式是得不到错误的数据的。要想获得绑定变量的内容，可以设置10046 EVENTS LEVEL 12，不过这种方式的代价太大，而且获得的最终trace文件也会十分巨大，想要从中寻找错误的数据，肯定也是十分的困难。

那么就只好采用一个笨办法，将数据在插入到目标表之前插入到一张记录表中。根据错误信息可以确定，导致错误产生的数据类型应该是NUMBER类型，因此记录表可以根据源表来生成，并将源表上NUMBER类型的精度都去掉，确保错误的数据可以正常的插入到记录表中：

SQL> create table shgov_order_bak as select * from shgov_order;

表已创建。

SQL> select column_name, data_type, data_precision, data_scaleITPUB个人空间.@C4\\E+_3S
  2  from user_tab_columns
,g[ m;] Pkt0  3  where table_name = 'SHGOV_ORDER_BAK'
j6Zc,h\0  4  and data_type = 'NUMBER';

COLUMN_NAME                    DATA_TYPE       DATA_PRECISION DATA_SCALE
2^/O Z;mq0------------------------------ --------------- -------------- ----------ITPUB个人空间 brUOT(M
TRADE_RATE                     NUMBER                      10          2
OnL:\/G|0ORDER_AMOUONT                  NUMBER
1Q aWP"`'a5Tf0SEND_AMOUNT                    NUMBER
{Yq\W.U?0RECEIVE_AMOUNT                 NUMBERITPUB个人空间a hm2ji
UNIT_PRICE                     NUMBER                      13          3ITPUB个人空间;\\-L? S~d1MS0h
MAX_PRICE                      NUMBER                      11          3
d AypE*mCC0MIN_PRICE                      NUMBER                      11          3ITPUB个人空间w1V? u]d7N&W
PRICE_RATE                     NUMBER                      12          3

已选择8行。

SQL> alter table shgov_order modify
,v(}M p5m2Q0  2  (trade_rate number,ITPUB个人空间]HA5Br~Nt5B4QX1@
  3  unit_price number,
)B){:I1zC0  4  max_price number,ITPUB个人空间A9DMyLT
  5  min_price number,ITPUB个人空间o#i[M1W)L
  6  price_rate number);

表已更改。

为了方便的找到最后一条错误记录，在记录表中添加一个记录ID：

SQL> alter table shgov_order_bak add id number;

表已更改。

SQL> create sequence s_id;

序列已创建。

下面建立一个自治事务的触发器，确保源表插入失败回滚后，记录表中的信息可以保留下来：

SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQLITPUB个人空间hNAM5W
  2  BEFORE INSERT ON SHGOV_ORDER
0x6n;XRU%a.C4g'}T0  3  FOR EACH ROWITPUB个人空间`f E&B-GlYWG+e
  4  DECLARE
/q5c9fw mD0e|0  5     PRAGMA AUTONOMOUS_TRANSACTION;ITPUB个人空间,nF!AN4eU
  6  BEGINITPUB个人空间3QNrE;SX;L9f f
  7     INSERT INTO SHGOV_ORDER_BAK VALUESITPUB个人空间T9uc'^ VDz*c
  8     (
V!F!P6{-Y0  9             :NEW.ORDER_ITEM_ID, :NEW.PLAT_ID, :NEW.PRODUCT_ID, :NEW.CODE,
N9~qmCCD2]0 10             :NEW.C_NAME_CHN, :NEW.MEDICAL_ID, :NEW.MEDICAL_CODE, :NEW.TRADE_NAME,
3BXO+Y9f]2O$j0 11             :NEW.USE_UNIT, :NEW.USED_NAME, :NEW.MANUFACTURE_ID,
bg-D:hW4B0 12             :NEW.MANUFACTURE_NAME, :NEW.MANUFACTURE_ABBR, :NEW.M_SPELL_ABBR,
v QdqT"]+b"^#B0 13             :NEW.STAND_RATE, :NEW.SPEC, :NEW.E_NAME_CHN, :NEW.JX_NAME_CHN,ITPUB个人空间 h&E:P^VH
 14             :NEW.WRAP_NAME, :NEW.TAX_PRICE, :NEW.NATIONAL_RETAIL_PRICE,
CU0d}9?j0 15             :NEW.TRADE_RATE, :NEW.MED_INSURE, :NEW.ORDER_ID, :NEW.ORDER_AMOUONT,ITPUB个人空间Lrp^r/UX p
 16             :NEW.SEND_AMOUNT, :NEW.RECEIVE_AMOUNT, :NEW.UNIT_PRICE,ITPUB个人空间hC)j"|sn'@
 17             :NEW.SOURCE_TYPE, :NEW.MAX_PRICE, :NEW.HIS_NAME, :NEW.HIS_ABBR,ITPUB个人空间.Q \4h]3C7h7Q;@!a
 18             :NEW.HIS_ID, :NEW.DEALER_NAME, :NEW.DEALER_ID, :NEW.DEALER_ABBR,ITPUB个人空间5k j R~$vS
 19             :NEW.MIN_PRICE, :NEW.SENDER_NAME, :NEW.SENDER_ID, :NEW.SENDER_ABBR,ITPUB个人空间z&a"K3l%n:x8G!e'x
 20             :NEW.CREATE_DATE, :NEW.SENDE_DATE, :NEW.PRICE_RATE, S_ID.NEXTVALITPUB个人空间B0f/s [UN3F
 21     );
;ZG i%O{b\g0 22     COMMIT;
_k7e(P Us0 23  END;
np+{n6s9K;OS0 24  /

触发器已创建

下面再次执行导入操作：

$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y

Import: Release 9.2.0.4.0 - Production on星期四5月8 00:19:39 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


2L!X]@.v};q0x8Kc0连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
e7a FE:Z |~2@7w0With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间ELK.R-`#_*ik+TG
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件

警告:此对象由SHGOV导出,而不是当前用户

已经完成ZHS16GBK字符集和AL16UTF16 NCHAR字符集中的导入
&Q'HW b.vY E0.正在将SHGOV的对象导入到TEST
[(s0z1C/Th0. .正在导入表                   "SHGOV_ORDER"
;sw&y)L9[ i0IMP-00058:遇到ORACLE错误1438
uT qm+CZ0ORA-01438:值大于此列指定的允许精确度
&B2O\^bo0ORA-06512:在"TEST.FIND_ERR_SQL", line 4ITPUB个人空间 dD we5njYDf%i
ORA-04088:触发器'TEST.FIND_ERR_SQL'执行过程中出错
-F K5~!N w7K0IMP-00028:上一个表的部分导入已回退:回退31322行ITPUB个人空间+QO(JK0?{4r kwo
IMP-00009:导出文件异常结束ITPUB个人空间/f`4i4X2o nl
成功终止导入，但出现警告。

发现错误信息发生了变化，居然插入记录表也报错。而记录表中所有的数值类型都已经是NUMBER类型，即使这样仍然会报错，看来数据确实比较奇怪。

看来只能将表的所有数值类型的字段改为VARCHAR2(4000)：

SQL> ALTER TABLE SHGOV_ORDER MODIFYITPUB个人空间 z(Z#^ \&}BKH n
  2  (
X5a @Vs a7i7A0  3     TRADE_RATE VARCHAR2(4000),ITPUB个人空间awfhu(n
  4     ORDER_AMOUONT VARCHAR2(4000),ITPUB个人空间f8z6NGV0y6Q.R
  5     SEND_AMOUNT VARCHAR2(4000),
8^,~0Y h} _!L0  6     RECEIVE_AMOUNT VARCHAR2(4000),ITPUB个人空间 [q[FA
  7     UNIT_PRICE VARCHAR2(4000),ITPUB个人空间J.x/u mKX ?-_
  8     MAX_PRICE VARCHAR2(4000),ITPUB个人空间%? P:lK4|VPs2P.O
  9     MIN_PRICE VARCHAR2(4000),
MK a8T%ut0 10     PRICE_RATE VARCHAR2(4000)
c\ Pg5W1s-m:r!c2j0 11  );

表已更改。

SQL> TRUNCATE TABLE SHGOV_ORDER_BAK;

表已截掉。

SQL> DROP SEQUENCE S_ID;

序列已丢弃。

SQL> CREATE SEQUENCE S_ID;

序列已创建。

再次执行导入：

$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y

Import: Release 9.2.0.4.0 - Production on星期四5月8 00:41:19 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


A(O\1ER9Ze0连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionITPUB个人空间s@J:WBo
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间3r'x5l3?egg k
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件

警告:此对象由SHGOV导出,而不是当前用户

已经完成ZHS16GBK字符集和AL16UTF16 NCHAR字符集中的导入
T].BgGs~;[,p0S0.正在将SHGOV的对象导入到TESTITPUB个人空间5r(i bqm-x{:X
. .正在导入表                   "SHGOV_ORDER"
whP f'uOS5]0IMP-00058:遇到ORACLE错误1722
$s-Ag:OwI!P'J5rE0ORA-01722:无效数字
o5c6yt!m/Eq` {9Wf0ORA-06512:在"TEST.FIND_ERR_SQL", line 4
!jm w;tyUT0V0ORA-04088:触发器'TEST.FIND_ERR_SQL'执行过程中出错ITPUB个人空间#t] v'NK-@9z
IMP-00028:上一个表的部分导入已回退:回退31322行ITPUB个人空间jL#F8z,Fnqa(N
IMP-00009:导出文件异常结束ITPUB个人空间Yf-| Z D,li
成功终止导入，但出现警告。

错误信息再次改变，看来这次是在将数值类型转化为NUMBER类型的时候出现了错误。基本上可以确定，问题多半是由于逻辑损坏造成存储的数据异常，目前这个值已经无法转换为NUMBER类型了。

 


导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报

TAG:

 

评分：0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar