获取导致导入失败的数据（五）

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

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

ITPUB个人空间QM v8|\&{h.JL$M,d
连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionITPUB个人空间q@OgF.smy'F
With the Partitioning, OLAP and Oracle Data Mining options
.^_%M0L'z4B#U0JServer Release 9.2.0.4.0 - Production

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

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

已经完成ZHS16GBK字符集和AL16UTF16 NCHAR字符集中的导入
Q9ml%Au0.正在将SHGOV的对象导入到TEST
0vm+H#g(]0IMP-00009:导出文件异常结束ITPUB个人空间9ow
i i_BD4z.h
成功终止导入，但出现警告。

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

SQL> CREATE TABLE SHGOV_ORDER_BAK 
ps2^#\*GX9L0  2  AS SELECT TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,ITPUB个人空间3C e,_ x I,I
  3  UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE FROM SHGOV_ORDER;

表已创建。

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

SQL> ALTER TABLE SHGOV_ORDER MODIFY
1@7_E^R0  2  (ITPUB个人空间X(Ne ue{
  3     TRADE_RATE NUMBER,ITPUB个人空间^(Sp)X@(vX3B
  4     UNIT_PRICE NUMBER,ITPUB个人空间5~(UE-X/G8E.^0@\
  5     MAX_PRICE NUMBER,ITPUB个人空间)d/u0_U5?z
CXK5I
  6     MIN_PRICE NUMBER,ITPUB个人空间~F,UMh(g9NIz
  7     PRICE_RATE NUMBER
;L1p+yX ]d0l2s@"T k0  8  );

表已更改。

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

SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQLITPUB个人空间?*K/P l(bJ5d
}TL
  2  BEFORE INSERT ON SHGOV_ORDER
0W-v3}\X"|k0  3  FOR EACH ROWITPUB个人空间
qx[,m@YR
[
  4  DECLAREITPUB个人空间-]6z5k:pd/V];W,?
  5     PRAGMA AUTONOMOUS_TRANSACTION;
#a'{9Yn x!PB)ZJS0  6  BEGIN
3\,U3I#]wR6k0  7     EXECUTE IMMEDIATE 'INSERT INTO SHGOV_ORDER_BAK
wF)WuB1_0  8     (
9xiZ d1EOi)Zb6S0  9             TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
*Q'[$KF"M4cQ0Os0 10             UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
zka V
A2b0 11     )
^z#T#_/kG)C]0 12     VALUESITPUB个人空间s eqY(k)b
 13     (ITPUB个人空间8Q K5_K+jSi
 14             ' || :NEW.TRADE_RATE || ', ' || :NEW.ORDER_AMOUONT || ', '
)]Z0s:Fl0o.P)\0 15             || :NEW.SEND_AMOUNT || ', ' || :NEW.RECEIVE_AMOUNT || ', '
;jp&O1x2x;M0 16             || :NEW.UNIT_PRICE || ', ' || :NEW.MAX_PRICE || ', '
Gp u6g2A u0 17             || :NEW.MIN_PRICE || ', ' || :NEW.PRICE_RATE || ')';ITPUB个人空间*K$W9Q9V#[PE}
 18     COMMIT;
M n-Q:?&h|EfQ0 19  END;
N Wl3NhIcv5R0 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.

&\p9_6pH+H+tb r0连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
6| q*k4Rbr&v c0With the Partitioning, OLAP and Oracle Data Mining options
PXQP x0JServer Release 9.2.0.4.0 - Production

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

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

已经完成ZHS16GBK字符集和AL16UTF16 NCHAR字符集中的导入ITPUB个人空间B$S;kD;LF7\
.正在将SHGOV的对象导入到TESTITPUB个人空间Q
B(vX? Bc
. .正在导入表                   "SHGOV_ORDER"ITPUB个人空间A2{)[,aZR!OH
IMP-00058:遇到ORACLE错误917
'SwX's0U1l!g#t+QJ0ORA-00917:缺少逗号
^CJTB
m2Op0ORA-06512:在"TEST.FIND_ERR_SQL", line 4
+huw!l8Cs3Q0ORA-04088:触发器'TEST.FIND_ERR_SQL'执行过程中出错
B9UQ&Q!r){A0IMP-00028:上一个表的部分导入已回退:回退31322行ITPUB个人空间~Ls.`FlCl
IMP-00009:导出文件异常结束
o9Y,q/f*f6ZX5N7m0成功终止导入，但出现警告。

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

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

SQL> CREATE SEQUENCE S_SEQ;

序列已创建。

SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQLITPUB个人空间/v$MUP4u Hw
  2  BEFORE INSERT ON SHGOV_ORDER
!`lMaCD0  3  FOR EACH ROWITPUB个人空间'Bs zT v
AT
  4  DECLAREITPUB个人空间C4x3{;[!D2v
  5     V_NUM NUMBER;ITPUB个人空间x0zV.u;A5}w/Uw{
  6  BEGIN
"e;J!EAn){4c4Y0  7     SELECT S_SEQ.NEXTVAL INTO V_NUM FROM DUAL;
!F {)v)@$xd}%Mo8D|X0  8     IF V_NUM = 31323 THENITPUB个人空间i4N U7`/]@&S[
  9             EXECUTE IMMEDIATE 'INSERT INTO SHGOV_ORDER_BAK
b-Qd
gC(k2l0 10             (ITPUB个人空间0n U%N%GQ
 11                     TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,ITPUB个人空间#S(L
Mv"VVi(Y-n
 12                     UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATEITPUB个人空间
EHeRAl N,{H
 13             )
p!tG*xw/e&o0 14             VALUES
t2dS9[6Q0 15             (ITPUB个人空间2x9x5w2g&~s'Z1hD5q@
 16                     ''' || :NEW.TRADE_RATE || ''', ''' || :NEW.ORDER_AMOUONT || ''', '''
.dz!n,oUJK5V;G0 17                     || :NEW.SEND_AMOUNT || ''', ''' || :NEW.RECEIVE_AMOUNT || ''', '''
W+hgFvNq0w9Nm0 18                     || :NEW.UNIT_PRICE || ''', ''' || :NEW.MAX_PRICE || ''', '''ITPUB个人空间
{1[(P7f#S
 19                     || :NEW.MIN_PRICE || ''', ''' || :NEW.PRICE_RATE || ''')';
'ZtELi0 20     END IF;ITPUB个人空间*hF.I [[9p4`k
 21  END;ITPUB个人空间"nx2Kv-F
 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.

4@InbU:C0连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionITPUB个人空间X(Y$B$\/q]}!{;C#~1d
With the Partitioning, OLAP and Oracle Data Mining options
[+pv;Wqy0JServer Release 9.2.0.4.0 - Production

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

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

已经完成ZHS16GBK字符集和AL16UTF16 NCHAR字符集中的导入ITPUB个人空间r,dE_@z-j
.正在将SHGOV的对象导入到TESTITPUB个人空间z[ p/BShO%?
. .正在导入表                   "SHGOV_ORDER"ITPUB个人空间BI~n;xOR!ba@
IMP-00058:遇到ORACLE错误1722
LN y2p)i1ZIb.W0ORA-01722:无效数字ITPUB个人空间I&L|5T9l#d
ORA-06512:在"TEST.FIND_ERR_SQL", line 6
)J)k8c7h5~Bb0ORA-04088:触发器'TEST.FIND_ERR_SQL'执行过程中出错
XdQyZ D0IMP-00028:上一个表的部分导入已回退:回退31322行ITPUB个人空间e|J7PY&Vt
IMP-00009:导出文件异常结束ITPUB个人空间(d)w*Iw wJb
成功终止导入，但出现警告。

检查V$SQL，看看是否捕获了问题SQL语句：

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

SQL_TEXT
"S)J#_P^0-----------------------------------------------------------------------------------------------------
o6kwD9`Z0INSERT 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也是一个可行的方法。