发表于: 2008.05.20 23:27
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/462396
---------------------------------------------------------------
前不久从一个数据库执行导出操作时报错，通过直接路径方式跳过后，导入时候再次报错。推测是由于源数据库出现的异常导致表中数据超过表定义的精度。
由于源数据库中错误记录已经被删除，因此只能想办法从导出的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也是一个可行的方法。
