通过整合多种Oracle技术来定位、分析并解决问题——一次Access数据库表导入Oracle的完整记录[转帖]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 3721 | 回复: 0

主题： 通过整合多种Oracle技术来定位、分析并解决问题——一次Access数据库表导入Oracle的完整记录[转帖]

wayne

注册用户

等级：中校
经验：1690
发帖：221
精华：0
注册：2011-7-21
状态：离线
发送短消息息给wayne

加好友发送短消息息给wayne

发消息

发表于：

2011-8-16 16:02:42 | [全部帖] [楼主帖]

楼主

我们了解许多Oracle的技术细节，但是真正出现问题的时候，却很难利用这些知识或技术来解决我们的问题。

这个例子是我在工作中碰到的一个例子，把它记录下来希望对大家有所启示，起到抛砖引玉的作用。

这个例子其实存在着很多变通的解决方法，我的这个解决方法很有可能是最复杂的一种，但这里着重想要说明的是一种一步步解决问题的思路。

好了，闲话说完了，开始抛砖。

今天碰到的一个问题是更新数据库中的一些记录，用来执行更新操作的数据源是一个Access数据库。

由于从来没有接触过Access数据库，因此打算首先将Access数据库的数据想办法导入到Oracle中。在Access的菜单中发现了导出这一项，打算导出成文本或EXCEL格式再导入的Oracle中。不过在导出菜单的保存类型的最后一项发现了ODBC数据库这中类型。我在本地已经建立ODBC的数据源，那么通过这种方法就应该直接可以导入到Oracle中了。

于是开始操作。导出“查询1”（Access的数据源）到本机的Oracle9i的ODBJ数据源，输入密码后，执行报错：

ODBC——调用失败。

[ORACLE][ODBC][Ora]ORA-01401：插入的值对于列过大(#1401)

虽然报错了，但是幸好报的是Oracle的错误。这个错误说明插入的列比表定义的列的范围要大。初步怀疑是通过ODBC建表时有些问题。

既然通过ODBC建表有问题，那么我就先建好合适的表，然后再导入。于是我根据Access中的数据估计每列的大小，然后在数据库中建好同名的表。

SQL> create table 查询1 ("product_id" CHAR(24), ORG_ID CHAR(24),
2 ID NUMBER, 通用名 VARCHAR2(200), 商品名 VARCHAR2(200), 剂型 VARCHAR2(200),
3 包装材质 VARCHAR2(200), 规格 VARCHAR2(500), 转换比 number, 质量层次 VARCHAR2(20),
4 单位 VARCHAR2(100), 供应价 NUMBER, 最高限价 NUMBER, 临时零售价 NUMBER,
5 生产企业 VARCHAR2(500), 投标人 VARCHAR2(200), 中标方式 VARCHAR2(20));

表已创建。

重复导入操作，这回出现的错误是：

ODBC——调用失败。

[ORACLE][ODBC][Ora]ORA-00905：名称已由现有对象使用(#905)

这个错误更加明显，表已经存在。在Access里面找了找，似乎没有发现可以重用已经存在的表的选项。只好放弃这种方式，将表删除。

SQL> drop table 查询1;

表已丢弃。

对于错误的产生的原因是推测出来的，希望能通过Oracle找到错误的真正原因。由于在Access这边是得不到create语句和insert语句的（也许可以，对Access不熟悉），因此希望从Oracle这方面入手来找到问题的原因。

首先想到的是用审计，但是审计似乎只记录操作类型，没有记录具体的语句。因此考虑是否可以通过LOGMINER找到建表的语句。从当前的错误信息看，错误应该是发生在INSERT的时候，也就是说Access通过ODBC已经完成了建表操作，但是由于插入发生了错误，所以又删除了表。因此，日志中应该会记录建表的语句。

SQL> SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SQL> ALTER SYSTEM SWITCH LOGFILE;

系统已更改。

再次执行导出。失败后：

SQL> SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
SQL> ALTER SYSTEM SWITCH LOGFILE;

系统已更改。

SQL> SELECT * FROM V$LOGFILE WHERE GROUP# = 2;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------
2 ONLINE E:ORACLEORADATAYANGTKREDO02.LOG
SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('E:ORACLEORADATAYANGTKREDO02.LOG', SYS.DBMS_LOGMNR.NEW)

PL/SQL 过程已成功完成。

SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

PL/SQL 过程已成功完成。

SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_NAME = '查询1';

SQL_REDO
-----------------------------------------------------

CREATE TABLE "查询1"("product_id" VARCHAR2(255),"ORG_ID" VARCHAR2(255),"ID" VARCHAR2(2),"通用名" VAR
CHAR2(255),"商品名" VARCHAR2(255),"剂型" VARCHAR2(255),"包装材质" VARCHAR2(255),"规格" VARCHAR2(255)
,"转换比" VARCHAR2(255),"质量层次" VARCHAR2(255),"单位" VARCHAR2(255),"供应价" VARCHAR2(4),"最高限价
" VARCHAR2(4),"临时零售价" VARCHAR2(4),"生产企业" VARCHAR2(255),"投标人" VARCHAR2(255),"中标方式" VA

RCHAR2(255));

DROP TABLE "查询1";

SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR

PL/SQL 过程已成功完成。

很显然，Access将所有的数值类型都错误的对应成了VARCHAR2类型，字段的宽度很小，导致了插入的错误。

这时，已经可以通过修改Access的数据类型或对应关系来解决问题了。但是我对Access不熟，因此我希望仍然可以通过在Oracle一端进行修改来解决问题。而且，Access是可以修改的，而有些情况下，导入程序这端是很难或无法进行修改的。

由于导入过程首先是建表、然后插入数据，这是两个事务，因此，在这两个事务之间修改表结构理论上是可行的。

决定建立一个触发器来修改表结构。需要建立一个基于CREATE的DDL触发器，通过建表语句来触发。不能建立为BEFORE INSERT的触发器，因为表还没有建立，无法在不存在的表上创建触发器。

SQL> CREATE OR REPLACE TRIGGER TRI_TEST AFTER CREATE ON SCHEMA
2 BEGIN

3 EXECUTE IMMEDIATE 'ALTER TABLE 查询1 modify ("product_id" CHAR(24), ORG_ID CHAR(24),
4 ID NUMBER, 通用名 VARCHAR2(200), 商品名 VARCHAR2(200), 剂型 VARCHAR2(200),
5 包装材质 VARCHAR2(200), 规格 VARCHAR2(500), 转换比 number, 质量层次 VARCHAR2(20),
6 单位 VARCHAR2(100), 供应价 NUMBER, 最高限价 NUMBER, 临时零售价 NUMBER,
7 生产企业 VARCHAR2(500), 投标人 VARCHAR2(200), 中标方式 VARCHAR2(20))';

8 END;
9 /

触发器已创建

SQL> CREATE TABLE "查询1"("product_id" VARCHAR2(255),"ORG_ID" VARCHAR2(255),"ID" VARCHAR2(2),
2 "通用名" VARCHAR2(255),"商品名" VARCHAR2(255),"剂型" VARCHAR2(255),"包装材质" VARCHAR2(255),
3 "规格" VARCHAR2(255),"转换比" VARCHAR2(255),"质量层次" VARCHAR2(255),"单位" VARCHAR2(255),
4 "供应价" VARCHAR2(4),"最高限价" VARCHAR2(4),"临时零售价" VARCHAR2(4),"生产企业" VARCHAR2(255),
5 "投标人" VARCHAR2(255),"中标方式" VARCHAR2(255));
CREATE TABLE "查询1"("product_id" VARCHAR2(255),"ORG_ID" VARCHAR2(255),"ID" VARCHAR2(2),
*
ERROR 位于第 1 行:
ORA-30512: 不能在事务处理超过一次修改YANGTK.查询1

首先建立一个AFTER CREATE的触发器，触发器只是简单修改一下表结构。但是由于存储过程中不能直接使用DDL语句，因此必须使用动态SQL语句。

而且，对于触发器工作情况的测试，就没有必要通过导入数据进行测试，只需要本地建表就可以测试出来了。建表的语句就是从LOGMINER中获取的。

但是出现下面的错误：

ORA-30512 cannot modify string.string more than once in a transaction
Cause: An attempt was made to modify an object more than once in a transaction. This error is usually caused by a DDL statement that fires a system trigger that tries to modify the same object. It can also happen when an attempt is made to perform more than one DDL operation on a queue table in the same transaction without issuing a commit between operations.
Action: Do not create system triggers that might modify an already modified object. Also, do not specify more than one DDL operation on a queue table in the same transaction.

错误是由于触发器和CREATE语句在同一个事务中，而这个事务两次修改了同一张表。

于是尝试使用自治事务：

SQL> CREATE OR REPLACE TRIGGER TRI_TEST AFTER CREATE ON SCHEMA
2  DECLARE
3  PRAGMA AUTONOMOUS_TRANSACTION;
4  BEGIN

5 EXECUTE IMMEDIATE 'ALTER TABLE 查询1 modify ("product_id" CHAR(24), ORG_ID CHAR(24),
6 ID NUMBER, 通用名 VARCHAR2(200), 商品名 VARCHAR2(200), 剂型 VARCHAR2(200),
7 包装材质 VARCHAR2(200), 规格 VARCHAR2(500), 转换比 number, 质量层次 VARCHAR2(20),
8 单位 VARCHAR2(100), 供应价 NUMBER, 最高限价 NUMBER, 临时零售价 NUMBER,
9 生产企业 VARCHAR2(500), 投标人 VARCHAR2(200), 中标方式 VARCHAR2(20))';

10 END;
11 /

触发器已创建

SQL> CREATE TABLE "查询1"("product_id" VARCHAR2(255),"ORG_ID" VARCHAR2(255),"ID" VARCHAR2(2),
2 "通用名" VARCHAR2(255),"商品名" VARCHAR2(255),"剂型" VARCHAR2(255),"包装材质" VARCHAR2(255),
3 "规格" VARCHAR2(255),"转换比" VARCHAR2(255),"质量层次" VARCHAR2(255),"单位" VARCHAR2(255),
4 "供应价" VARCHAR2(4),"最高限价" VARCHAR2(4),"临时零售价" VARCHAR2(4),"生产企业" VARCHAR2(255),
5 "投标人" VARCHAR2(255),"中标方式" VARCHAR2(255));
CREATE TABLE "查询1"("product_id" VARCHAR2(255),"ORG_ID" VARCHAR2(255),"ID" VARCHAR2(2),
*
ERROR 位于第 1 行:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-04020: 尝试锁定对象 YANGTK.查询1 时检测到死锁
ORA-06512: 在line 4

这个错误是由于自治事务执行时CREATE语句还没有结束，因此自治事务必须等CREATE事务完成后才能执行ALTER TABLE语句，而触发器是事务的一部分，因此CREATE语句又在等待自治事务的执行，所以构成了死锁。

解决这个问题的办法是必须将CREATE和ALTER分别放到两个不相关的事务中，因此，我们只能通过JOB来实现：

SQL> CREATE OR REPLACE TRIGGER TRI_TEST AFTER CREATE ON SCHEMA
2  DECLARE
3  PRAGMA AUTONOMOUS_TRANSACTION;
4  V_JOB NUMBER;
5  BEGIN
6  DBMS_JOB.SUBMIT(V_JOB, 'BEGIN
7 EXECUTE IMMEDIATE ''ALTER TABLE 查询1 modify ("product_id" CHAR(24), ORG_ID CHAR(24),

8 ID NUMBER, 通用名 VARCHAR2(200), 商品名 VARCHAR2(200), 剂型 VARCHAR2(200),
9 包装材质 VARCHAR2(200), 规格 VARCHAR2(500), 转换比 number, 质量层次 VARCHAR2(20),
10 单位 VARCHAR2(100), 供应价 NUMBER, 最高限价 NUMBER, 临时零售价 NUMBER,
11 生产企业 VARCHAR2(500), 投标人 VARCHAR2(200), 中标方式 VARCHAR2(20))'';

12  END;', SYSDATE);
13  COMMIT;
14  END;
15  /

触发器已创建

表已创建。

SQL> DESC 查询1
名称是否为空? 类型

------------------------------- -------- -------------------------
product_id                               CHAR(24)
ORG_ID                                   CHAR(24)
ID                                       NUMBER

通用名                                   VARCHAR2(200)
商品名                                   VARCHAR2(200)
剂型                                     VARCHAR2(200)
包装材质                                 VARCHAR2(200)
规格                                     VARCHAR2(500)
转换比                                   NUMBER
质量层次                                 VARCHAR2(20)
单位                                     VARCHAR2(100)
供应价                                   NUMBER
最高限价                                 NUMBER
临时零售价                               NUMBER
生产企业                                 VARCHAR2(500)
投标人                                   VARCHAR2(200)
中标方式                                 VARCHAR2(20)

看来这个方法是可行的。

再次执行导入操作，结果错误依旧。

SQL> SELECT JOB, FAILURES FROM USER_JOBS;
JOB FAILURES
---------- ----------
19 1
SQL> EXEC DBMS_JOB.REMOVE(19);

PL/SQL 过程已成功完成。

SQL> COMMIT;

提交完成。

Alert_yangtk.log文件中包含下面的错误：

Fri Apr 15 14:34:46 2005
Errors in file e:oracleadminyangtkudumpyangtk_j000_2792.trc:

ORA-12012: 自动执行作业 19 出错

ORA-00942: 表或视图不存在

ORA-06512: 在line 2

而这个错误是由于Oracle的JOB机制造成的。从Oracle9i开始，后台有个CJQ0进程，CJQ0进程负责唤醒job进程，由于采用了这种机制，9i中的JOB总会比设置的NEXT_DATE晚几秒。因此到JOB去执行ALTER TABLE时，导入过程已经报错并结束了。

因此，需要让导入过程等待JOB进程一会。

SQL> CONN / AS SYSDBA

已连接。

SQL> GRANT EXECUTE ON DBMS_LOCK TO YANGTK;

授权成功。

SQL> CONN YANGTK/YANGTK

已连接。

12  END;', SYSDATE);
13  COMMIT;
14  DBMS_LOCK.SLEEP(2);
15  END;
16  /

触发器已创建

重新执行导入操作，仍然报错，看来这个时间不是很好把握，时间太长或太短都会失败，又重试了两三次后，终于成功导入。

虽然问题解决了，但是毕竟要重试几次才能成功。而且已经费了很大功夫，不在乎在多花些时间完善一下。

SQL> CREATE OR REPLACE PROCEDURE P_TEST AS
2 V_RESULT NUMBER DEFAULT 0;
3 BEGIN

4 SELECT COUNT(*) INTO V_RESULT FROM USER_TABLES WHERE TABLE_NAME = '查询1';

5 WHILE (V_RESULT = 0) LOOP

6 SELECT COUNT(*) INTO V_RESULT FROM USER_TABLES WHERE TABLE_NAME = '查询1';

7 DBMS_LOCK.SLEEP(0.1);
8 END LOOP;

9   EXECUTE IMMEDIATE 'ALTER TABLE 查询1 modify ("product_id" CHAR(24), ORG_ID CHAR(24),
10    ID NUMBER, 通用名 VARCHAR2(200), 商品名 VARCHAR2(200), 剂型 VARCHAR2(200),
11    包装材质 VARCHAR2(200), 规格 VARCHAR2(500), 转换比 number, 质量层次 VARCHAR2(20),
12    单位 VARCHAR2(100), 供应价 NUMBER, 最高限价 NUMBER, 临时零售价 NUMBER,
13    生产企业 VARCHAR2(500), 投标人 VARCHAR2(200), 中标方式 VARCHAR2(20))';

14 END;
15 /

过程已创建。

SQL> CREATE OR REPLACE TRIGGER TRI_TEST AFTER CREATE ON SCHEMA
2  DECLARE
3  PRAGMA AUTONOMOUS_TRANSACTION;
4  V_JOB NUMBER;
5  BEGIN
6  DBMS_JOB.SUBMIT(V_JOB, 'P_TEST;', SYSDATE);
7  COMMIT;
8  DBMS_LOCK.SLEEP(2);
9  END;
10  /

触发器已创建

SQL> DROP TABLE 查询1;

表已丢弃。

这回导入操作一次成功。

SQL> SELECT COUNT(*) FROM 查询1;

COUNT(*)
----------
8342

现在回头看一下，只是运用了一个基于DDL的触发器、通过这个触发器建立了一个JOB，通过JOB中调用了一个存储过程而已。

但是从问题的诊断到解决分别用到了DDL、DML语句的概念、事务的概念、LOGMINER的使用、基于DDL的触发器、自持事务的概念、DBMS_JOB包的运用、动态SQL语句、DBMS_LOCK包的使用、而且由于使用了动态SQL，还可能会碰到角色权限失效的问题。

因此，当你遇到一个以前从未碰到过的问题的时候，其实就是考验你对Oracle掌握的程度。你对Oracle的概念了解的越清晰、功能了解的越多，定位问题就越容易，而且可供你选择的解决方法也就越多。