来自 热门技术 2018-09-19 16:35 的文章

获取导致导入失败的数据(五)

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

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

检验通过检查共享池的方法能否得到问题SQL。

获取导致导入失败的数据:http://yangtingkun.itpub.net/post/468/461401

获取导致导入失败的数据(二):http://yangtingkun.itpub.net/post/468/461600

获取导致导入失败的数据(三):http://yangtingkun.itpub.net/post/468/461660

获取导致导入失败的数据(四):http://yangtingkun.itpub.net/post/468/462331

BEFORE触发器修正数据错误:http://yangtingkun.itpub.net/post/468/461506

 

 

虽然在第三篇文章中,已经实现了目标数据的导入,但是并未采用当时计划使用的访问共享池获取错误SQL的方式。

现在打算验证一下,通过访问V$SQL的方式是否能够达到同样的获取失败SQL的目的。

下面删除SHGOV_ORDER和SHGOV_ORDER_BAK表,重新建立测试环境:

SQL> DROP TABLE SHGOV_ORDER;    

表已丢弃。

SQL> DROP TABLE SHGOV_ORDER_BAK;

表已丢弃。

利用imp工具构建SHGOV_ORDER表:

[oracle@bjtest data]$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y rows=n

Import: Release 9.2.0.4.0 - Production on 星期二 5月 20 01:26:18 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
IMP-00009: 导出文件异常结束
成功终止导入,但出现警告。

根据第三篇文章中的验证,即使插入数据的精度超过表的限制,这条报错的SQL也会被共享池所记录下来,下面就可以用原表的NUMBRE类型来构建SHGOV_ORDER_BAK表,从而导致错误发生在触发器中:

SQL> CREATE TABLE SHGOV_ORDER_BAK 
  2  AS SELECT TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
  3  UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE FROM SHGOV_ORDER;

表已创建。

根据第二篇文章的测试已经可以确定,当导入时数据的长度超过表字段的限制时,即使是BEFORE触发器也不会触发。因此需要修改SHGOV_ORDER表的所有NUMBER类型字段,确保触发器触发之前的数据类型检测可以通过,使得BEFORE触发器可以触发。

SQL> ALTER TABLE SHGOV_ORDER MODIFY
  2  (
  3     TRADE_RATE NUMBER,
  4     UNIT_PRICE NUMBER,
  5     MAX_PRICE NUMBER,
  6     MIN_PRICE NUMBER,
  7     PRICE_RATE NUMBER
  8  );

表已更改。

下面创建一个BEFORE INSERT触发器,采用动态的方式来插入数据,否则V$SQL记录的仍然是绑定变量的方式:

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  /

触发器已创建

为了避免共享池中已经存在的SQL的影响,导入前先清空共享池:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系统已更改。

下面可以尝试导入:

[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月 20 17:56:25 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 错误 917
ORA-00917: 缺少逗号
ORA-06512: 在"TEST.FIND_ERR_SQL", line 4
ORA-04088: 触发器 'TEST.FIND_ERR_SQL' 执行过程中出错
IMP-00028: 上一个表的部分导入已回退: 回退 31322 行
IMP-00009: 导出文件异常结束
成功终止导入,但出现警告。

现在的错误是缺少逗号,也就是说在进行动态SQL的解析的时候出现了错误,而只有解析成功的SQL才能放到共享池中。

看来还需要通过修改触发器的工作方式,不过如果改成字符串方式,那么获得的记录就太多了,为了得到需要的记录,修改一下触发器的实现:

SQL> CREATE SEQUENCE S_SEQ;

序列已创建。

SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQL
  2  BEFORE INSERT ON SHGOV_ORDER
  3  FOR EACH ROW
  4  DECLARE
  5     V_NUM NUMBER;
  6  BEGIN
  7     SELECT S_SEQ.NEXTVAL INTO V_NUM FROM DUAL;
  8     IF V_NUM = 31323 THEN
  9             EXECUTE IMMEDIATE 'INSERT INTO SHGOV_ORDER_BAK
 10             (
 11                     TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
 12                     UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
 13             )
 14             VALUES
 15             (
 16                     ''' || :NEW.TRADE_RATE || ''', ''' || :NEW.ORDER_AMOUONT || ''', '''
 17                     || :NEW.SEND_AMOUNT || ''', ''' || :NEW.RECEIVE_AMOUNT || ''', '''
 18                     || :NEW.UNIT_PRICE || ''', ''' || :NEW.MAX_PRICE || ''', '''
 19                     || :NEW.MIN_PRICE || ''', ''' || :NEW.PRICE_RATE || ''')';
 20     END IF;
 21  END;
 22  /

触发器已创建

清除环境:

SQL> TRUNCATE TABLE SHGOV_ORDER_BAK;

表已截掉。

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系统已更改。

下面再次进行导入操作:

[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月 20 18:32:36 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 6
ORA-04088: 触发器 'TEST.FIND_ERR_SQL' 执行过程中出错
IMP-00028: 上一个表的部分导入已回退: 回退 31322 行
IMP-00009: 导出文件异常结束
成功终止导入,但出现警告。

检查V$SQL,看看是否捕获了问题SQL语句:

SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'INSERT INTO SHGOV_ORDER_BAK%';

SQL_TEXT
-----------------------------------------------------------------------------------------------------
INSERT INTO SHGOV_ORDER_BAK   (    TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,    UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE   )   VALUES   (    '-53525351485153525352.535299ED98~m~m77311951', '-311951531753535353535349535353535353535345000000000000000000000000000000000000', '-505353535353535252524948536449.535152534552', '-000000000000000000000000000000000000', '-.00000000000000195153175353535353535253535353535352505049', '.00000000000000000000000000000033293311.,', '-~', '-515550504648000000000000') 

显然,通过共享池捕获错误SQL也是一个可行的方法。

 

 

 

本文源自: AG88平台