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

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

连接到: 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

连接到:
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

连接到: 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

连接到: 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类型了。

ITPub博客

获取导致导入失败的数据

最新文章