前不久从一个数据库执行导出操作时报错，通过直接路径方式跳过后，导入时候再次报错。推测是由于源数据库出现的异常导致表中数据超过表定义的精度。
由于源数据库中错误记录已经被删除，因此只能想办法从导出的dmp文件中获取错误的记录。
导出、导入过程的描述可以参考：
导入时报错如下：
$ 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.
连接到: 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"
IMP-00058:遇到ORACLE错误1438
ORA-01438:值大于此列指定的允许精确度IMP-00028:上一个表的部分导入已回退:回退31322行IMP-00009:导出文件异常结束成功终止导入，但出现警告。
根据这个错误，是无法判断问题到底出现在哪一列上，也看不到导致错误数据。不过由于导出过程中出现了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.
连接到:
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> CREATE TABLE T_LOG (EXECUTE_DATE DATE, SQL_STATMENT CLOB);
表已创建。
SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQL AFTER SERVERERROR ON DATABASE
2 DECLARE
3 V_SQL_OUT ORA_NAME_LIST_T;
4 V_NUM NUMBER;
5 V_SQL_STATMENT VARCHAR2(32767);
6 BEGIN
7 IF IS_SERVERERROR(1438) THEN
8 V_NUM := ORA_SQL_TXT(V_SQL_OUT);
9 FOR I IN 1 .. V_NUM LOOP
10 V_SQL_STATMENT := V_SQL_STATMENT || V_SQL_OUT(I);
11 END LOOP;
12 INSERT INTO T_LOG (EXECUTE_DATE, SQL_STATMENT)
13 VALUES (SYSDATE, V_SQL_STATMENT);
14 END IF;
15 END;
16 /
触发器已创建
再次运行导入命令后，查询T_LOG表中的记录：
SQL> SET LONG 10000
SQL> SELECT SQL_STATMENT FROM T_LOG;
SQL_STATMENT
------------------------------------------------------------------------------
INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "SHGOV_ORDER" ("ORDER_ITEM_ID", "PLAT_ID
", "PRODUCT_ID", "CODE", "C_NAME_CHN", "MEDICAL_ID", "MEDICAL_CODE", "TRADE_NAME
", "USE_UNIT", "USED_NAME", "MANUFACTURE_ID", "MANUFACTURE_NAME", "MANUFACTURE_A
BBR", "M_SPELL_ABBR", "STAND_RATE", "SPEC", "E_NAME_CHN", "JX_NAME_CHN", "WRAP_N
AME", "TAX_PRICE", "NATIONAL_RETAIL_PRICE", "TRADE_RATE", "MED_INSURE", "ORDER_I
D", "ORDER_AMOUONT", "SEND_AMOUNT", "RECEIVE_AMOUNT", "UNIT_PRICE", "SOURCE_TYPE
", "MAX_PRICE", "HIS_NAME", "HIS_ABBR", "HIS_ID", "DEALER_NAME", "DEALER_ID", "D
EALER_ABBR", "MIN_PRICE", "SENDER_NAME", "SENDER_ID", "SENDER_ABBR", "CREATE_DAT
E", "SENDE_DATE", "PRICE_RATE") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10,
:11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26,
:27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42,
: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_scale
2 from user_tab_columns
3 where table_name = 'SHGOV_ORDER_BAK'
4 and data_type = 'NUMBER';
COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
------------------------------ --------------- -------------- ----------
TRADE_RATE NUMBER 10 2
ORDER_AMOUONT NUMBER
SEND_AMOUNT NUMBER
RECEIVE_AMOUNT NUMBER
UNIT_PRICE NUMBER 13 3
MAX_PRICE NUMBER 11 3
MIN_PRICE NUMBER 11 3
PRICE_RATE NUMBER 12 3
已选择8行。
SQL> alter table shgov_order modify
2 (trade_rate number,
3 unit_price number,
4 max_price number,
5 min_price number,
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_SQL
2 BEFORE INSERT ON SHGOV_ORDER
3 FOR EACH ROW
4 DECLARE
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 INSERT INTO SHGOV_ORDER_BAK VALUES
8 (
9 :NEW.ORDER_ITEM_ID, :NEW.PLAT_ID, :NEW.PRODUCT_ID, :NEW.CODE,
10 :NEW.C_NAME_CHN, :NEW.MEDICAL_ID, :NEW.MEDICAL_CODE, :NEW.TRADE_NAME,
11 :NEW.USE_UNIT, :NEW.USED_NAME, :NEW.MANUFACTURE_ID,
12 :NEW.MANUFACTURE_NAME, :NEW.MANUFACTURE_ABBR, :NEW.M_SPELL_ABBR,
13 :NEW.STAND_RATE, :NEW.SPEC, :NEW.E_NAME_CHN, :NEW.JX_NAME_CHN,
14 :NEW.WRAP_NAME, :NEW.TAX_PRICE, :NEW.NATIONAL_RETAIL_PRICE,
15 :NEW.TRADE_RATE, :NEW.MED_INSURE, :NEW.ORDER_ID, :NEW.ORDER_AMOUONT,
16 :NEW.SEND_AMOUNT, :NEW.RECEIVE_AMOUNT, :NEW.UNIT_PRICE,
17 :NEW.SOURCE_TYPE, :NEW.MAX_PRICE, :NEW.HIS_NAME, :NEW.HIS_ABBR,
18 :NEW.HIS_ID, :NEW.DEALER_NAME, :NEW.DEALER_ID, :NEW.DEALER_ABBR,
19 :NEW.MIN_PRICE, :NEW.SENDER_NAME, :NEW.SENDER_ID, :NEW.SENDER_ABBR,
20 :NEW.CREATE_DATE, :NEW.SENDE_DATE, :NEW.PRICE_RATE, S_ID.NEXTVAL
21 );
22 COMMIT;
23 END;
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.
连接到: 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"
IMP-00058:遇到ORACLE错误1438
ORA-01438:值大于此列指定的允许精确度ORA-06512:在"TEST.FIND_ERR_SQL", line 4
ORA-04088:触发器'TEST.FIND_ERR_SQL'执行过程中出错IMP-00028:上一个表的部分导入已回退:回退31322行IMP-00009:导出文件异常结束成功终止导入，但出现警告。
发现错误信息发生了变化，居然插入记录表也报错。而记录表中所有的数值类型都已经是NUMBER类型，即使这样仍然会报错，看来数据确实比较奇怪。
看来只能将表的所有数值类型的字段改为VARCHAR2(4000)：
SQL> ALTER TABLE SHGOV_ORDER MODIFY
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> 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.
连接到: 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"
IMP-00058:遇到ORACLE错误1722
ORA-01722:无效数字ORA-06512:在"TEST.FIND_ERR_SQL", line 4
ORA-04088:触发器'TEST.FIND_ERR_SQL'执行过程中出错IMP-00028:上一个表的部分导入已回退:回退31322行IMP-00009:导出文件异常结束成功终止导入，但出现警告。
错误信息再次改变，看来这次是在将数值类型转化为NUMBER类型的时候出现了错误。基本上可以确定，问题多半是由于逻辑损坏造成存储的数据异常，目前这个值已经无法转换为NUMBER类型了。
1333
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
