解决imp全库导入报错

53 阅读 0 评论 35 点赞

我是靠谱客的博主美满马里奥，最近开发中收集的这篇文章主要介绍解决imp全库导入报错，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

全库导入操作一直是我比较头疼的东西，一般情况下，我会尽量避免这种操作。但是对于有些情况，全库导入又几乎是唯一的选择，比如跨平台的数据库迁移、大版本升级等操作。

其实全库导入操作说起来很简单，令人比较头疼的是，导入过程中几乎不可避免的会出现大量的错误信息，这些错误有的可以忽略，有的可能造成系统的异常，本文试图通过各种方法尽量减少全库导入时错误的数量。

如果某些表空间不存在的话，全库导入操作会首先尝试创建表空间。所以在全库导入的时候，要么提前建立好所有必须的表空间，要么确保建立表空间的路径是存在的，且oracle用户拥有读写权限。

如果导入数据库采用默认安装的话，那么直接导入会产生什么问题呢：

$ imp "sys as sysdba" file=testmv_full.dmp full=y buffer=20480000 ignore=y log=testmv_full.log

用于大量重复用户存在，会报很多的唯一约束冲突的错误，不但会严重影响导入效率，而且产生包含大量错误信息的日志也会增加排错的难度和工作量。

更严重的是，在低于9205的一些版本中，导入操作会中途失败。具体情况可以参考：http://yangtingkun.itpub.net/post/468/214725

因此，为了避免上面提到的那些错误，尝试将那些会造成冲突的用户清除掉。删除用户列表为：

DROP USER ANONYMOUS CASCADE;
DROP USER OUTLN CASCADE;
DROP USER WMSYS CASCADE;
DROP USER ORDSYS CASCADE;
DROP USER ORDPLUGINS CASCADE;
DROP USER MDSYS CASCADE;
DROP USER CTXSYS CASCADE;
DROP USER XDB CASCADE;
DROP USER WKSYS CASCADE;
DROP USER WKPROXY CASCADE;
DROP USER ODM CASCADE;
DROP USER ODM_MTR CASCADE;
DROP USER OLAPSYS CASCADE;
DROP USER HR CASCADE;
DROP USER OE CASCADE;
DROP USER PM CASCADE;
DROP USER SH CASCADE;
DROP USER QS_ADM CASCADE;
DROP USER QS CASCADE;
DROP USER QS_WS CASCADE;
DROP USER QS_ES CASCADE;
DROP USER QS_OS CASCADE;
DROP USER QS_CBADM CASCADE;
DROP USER QS_CB CASCADE;
DROP USER QS_CS CASCADE;

删除用户后，使用同样的导入命令，错误大大减少。

检查日志文件，其中以IMP-开头的错误有2319个。而以ORA-开头的错误有1113个。其中发现大部分错误为ORA-00001，错误数为972个。都是SYSTEM用户下的表造成的。

于是，尝试在上面的基础上，删除SYSTEM用户下包含数据的表中的数据，重新执行导入操作，删除SYSTEM用户数据脚本如下：

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_CNT NUMBER := 0;
3 V_DEL NUMBER := 0;
4 V_LOOP NUMBER := 0;
5 V_RESULT NUMBER := 0;
6 BEGIN
7 <<LABLE_FOR_LOOP>>
8 FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
9 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || I.TABLE_NAME INTO V_RESULT;
10 IF V_RESULT != 0 THEN
11 BEGIN
12 V_CNT := V_CNT + 1;
13 EXECUTE IMMEDIATE 'DELETE ' || I.TABLE_NAME;
14 V_DEL := V_DEL + 1;
15 EXCEPTION
16 WHEN OTHERS THEN
17 NULL;
18 END;
19 END IF;
20 END LOOP;
21 V_LOOP := V_LOOP + 1;
22 DBMS_OUTPUT.PUT_LINE(V_LOOP || ':' || ' COUNT ' || V_CNT || ', DEL ' || V_DEL);
23 IF V_CNT != V_DEL THEN
24 V_CNT := 0;
25 V_DEL := 0;
26 GOTO LABLE_FOR_LOOP;
27 END IF;
28 END;
29 /
1: COUNT 10, DEL 10

PL/SQL 过程已成功完成。

SQL> COMMIT;

提交完成。

下面重新执行导入工作。

这次错误信息进一步减少，其中IMP-错误还有375个，ORA-错误还有135个。检查错误信息中发现开始的时候遇到了很多用户不存在的问题。于是尝试删除用户后对象后，重新建立用户。

SQL> DECLARE
2 TYPE T_VARCHAR_TAB IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 V_DROP_STR T_VARCHAR_TAB;
4 V_CREATE_STR T_VARCHAR_TAB;
5 BEGIN
6 SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) BULK COLLECT INTO V_CREATE_STR
7 FROM DBA_USERS
8 WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP');
9 SELECT 'DROP USER ' || USERNAME || ' CASCADE' BULK COLLECT INTO V_DROP_STR
10 FROM DBA_USERS
11 WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP');
12 FOR I IN 1..V_DROP_STR.COUNT LOOP
13 EXECUTE IMMEDIATE V_DROP_STR(I);
14 END LOOP;
15 FOR I IN 1..V_CREATE_STR.COUNT LOOP
16 EXECUTE IMMEDIATE V_CREATE_STR(I);
17 END LOOP;
18 END;
19 /

PL/SQL 过程已成功完成。

SQL> COMMIT;

提交完成。

下面再次进行导入工作。错误进一步减少。这时IMP-错误还剩351个，ORA-错误还剩85个。

上面是减少哪些可能造成危害的错误。而接下来目的是尽可能减少所有的错误。有些错误完全可以忽略，没有必要一定消除干净，本文纯粹以技术探讨为主，真正进行全库导入时，没有必要仿效。

观察testmv_full.log发现了一些IMP-00015错误：IMP-00015: 由于对象已存在，下列语句失败。

这些错误是由于同名同义词已经存在造成的，简单删除掉这些同名同义词就可以了。

在日志文档中还出现了下面的错误信息：

IMP-00017: 由于 ORACLE 的 6550 错误，以下的语句失败
"BEGIN "
""
"DECLARE "
" dummy integer;"
"BEGIN"
" select 1 into dummy from dual "
" where exists (select 1 from all_synonyms"
" where owner = 'PUBLIC' and synonym_name = 'DBMS_WM');"
"EXCEPTION "
" when NO_DATA_FOUND then "
" RAISE_APPLICATION_ERROR(-20000, 'OWM Not Installed');"
"END;"
""
"IF (sys.ltadm.existnonliveworkspaces OR"
"sys.ltadm.existversionedtabs) "
"THEN"
" SYS.WM_ERROR.RAISEERROR(SYS.LT.WM_ERROR_189_NO);"
"ELSE"
""
"begin"
"delete from wmsys.wm$workspaces_table;"
"exception when others then null;"
"end;"
""
"begin"
"delete from wmsys.wm$workspace_priv_table;"
"exception when others then null;"
"end;"
""
"begin"
"delete from wmsys.wm$version_hierarchy_table ;"
"exception when others then null;"
"end;"
""
"begin"
"execute immediate 'drop sequence wmsys.wm$up_del_trig_name_sequence' ; "
"exception when others then null;"
"end;"
""
"begin"
"execute immediate 'drop sequence wmsys.wm$insteadof_trigs_sequence' ; "
"exception when others then null;"
"end;"
""
"begin "
"execute immediate 'drop sequence wmsys.wm$lock_sequence' ; "
"exception when others then null;"
"end;"
""
"begin"
"execute immediate 'drop sequence wmsys.wm$vtid' ; "
"exception when others then null;"
"end;"
""
"begin"
"execute immediate 'drop sequence wmsys.wm$adt_sequence' ; "
"exception when others then null;"
"end;"
""
"begin"
"execute immediate 'drop sequence wmsys.wm$version_sequence' ; "
"exception when others then null;"
"end;"
""
"begin"
"execute immediate 'drop sequence wmsys.wm$row_sync_id_sequence' ; "
"exception when others then null;"
"end;"
""
"begin"
"execute immediate 'drop sequence wmsys.wm$udtrig_dispatcher_sequence' ; "
"exception when others then null;"
"end;"
""
"execute immediate 'create sequence wmsys.wm$up_del_trig_name_sequence start"
" with 3 nocache';"
"execute immediate 'create sequence wmsys.wm$insteadof_trigs_sequence start "
"with 3 nocache';"
"execute immediate 'create sequence wmsys.wm$lock_sequence start with 6 incr"
"ement by 2 nocache';"
"execute immediate 'create sequence wmsys.wm$vtid start with 3 nocache';"
"execute immediate 'create sequence wmsys.wm$adt_sequence start with 3 nocac"
"he';"
"execute immediate 'create sequence wmsys.wm$version_sequence start with 3 n"
"ocache';"
"execute immediate 'create sequence wmsys.wm$row_sync_id_sequence start with"
" 13 nocache';"
"execute immediate 'create sequence wmsys.wm$udtrig_dispatcher_sequence star"
"t with 3 nocache'; "
"END IF ;"
""
"COMMIT; END;"
IMP-00003: 遇到 ORACLE 错误 6550
ORA-06550: 第 21 行, 第 19 列:
PL/SQL: ORA-00942: 表或视图不存在
ORA-06550: 第 21 行, 第 1 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 26 行, 第 19 列:
PL/SQL: ORA-00942: 表或视图不存在
ORA-06550: 第 26 行, 第 1 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 31 行, 第 19 列:
PL/SQL: ORA-00942: 表或视图不存在
ORA-06550: 第 31 行, 第 1 列:
PL/SQL: SQL Statement ignored

这是由于匿名块中访问的表不存在造成的，于是，在删除用户时，忽略WMSYS用户，然后重新执行导入操作：

SQL> DECLARE
2 TYPE T_VARCHAR_TAB IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 V_DROP_STR T_VARCHAR_TAB;
4 V_CREATE_STR T_VARCHAR_TAB;
5 BEGIN
6 SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) BULK COLLECT INTO V_CREATE_STR
7 FROM DBA_USERS
8 WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'WMSYS');
9 SELECT 'DROP USER ' || USERNAME || ' CASCADE' BULK COLLECT INTO V_DROP_STR
10 FROM DBA_USERS
11 WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'WMSYS');
12 FOR I IN 1..V_DROP_STR.COUNT LOOP
13 EXECUTE IMMEDIATE V_DROP_STR(I);
14 END LOOP;
15 FOR I IN 1..V_CREATE_STR.COUNT LOOP
16 EXECUTE IMMEDIATE V_CREATE_STR(I);
17 END LOOP;
18 END;
19 /

PL/SQL 过程已成功完成。

SQL> DROP PUBLIC SYNONYM GENREMOTETASKINTERRUPTEDEXCEPT;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENREMOTEOBJECTCLOSEDEXCEPTION;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCONNECTIONPARAMETERINFOSEQU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENREMOTEAUTHENTICATIONEXCEPTI;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENREMOTEAPIVERSIONMISMATCHEXC;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENMETADATAPROPERTYVALUESUNION;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENMETADATAPROPERTYBAGSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCONNECTIONPARAMETERINFOSTRU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCONNECTIONPARAMETERTYPEENUM;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENREMOTESPECIFICATIONUPDATENE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCOMPOUNDCURSORBLOCKSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENPARENTSTARTENDBLOCKSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENINVALIDINDEXSPECIFICATIONEX;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENINCLUDEDDEPENDENCYBLOCKSTRU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENDEPENDENCYBLOCKSEQUENCESEQU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCURSORSPECIFIERSEQUENCESEQU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENRECURSIVEJOINDEFINITIONSTRU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCONSTANTLISTDEFINITIONSTRUC;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLREMOTETASKINTERRUPTEDEXCEPT;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLMETADATAPROPERTYVALUESUNION;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLMETADATAPROPERTYBAGSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLREMOTEAUTHENTICATIONEXCEPTI;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLREMOTEAPIVERSIONMISMATCHEXC;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLCONNECTIONPARAMETERINFOSEQU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLCONNECTIONPARAMETERTYPEENUM;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLCONNECTIONPARAMETERINFOSTRU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLPARENTSTARTENDBLOCKSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLINCLUDEDDEPENDENCYBLOCKSTRU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLREMOTESPECIFICATIONUPDATENE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLINVALIDINDEXSPECIFICATIONEX;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLDEPENDENCYBLOCKSEQUENCESEQU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLCOMPOUNDCURSORBLOCKSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLCONSTANTLISTDEFINITIONSTRUC;

同义词已删除。

导入完成后，发现ORA-错误77个，IMP-错误312个。

忽略了WMSYS用户果然有效，但是由于数据没有清除干净，在导入的时候报了唯一约束冲突的错误，于是修改删除SYSTEM用户数据的匿名块，使之同时删除WMSYS用户下的数据。同时注意到SYSTEM表中由于存在很多“ORA-02264: 名称已被一现有约束条件占用”的错误，于是通过匿名块删除这些重复的错误。

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_CNT NUMBER := 0;
3 V_DEL NUMBER := 0;
4 V_LOOP NUMBER := 0;
5 V_RESULT NUMBER := 0;
6 BEGIN
7 <<LABLE_FOR_LOOP>>
8 FOR I IN (SELECT OWNER || '.' || TABLE_NAME TABLE_NAME FROM DBA_TABLES WHERE OWNER IN ('SYSTEM
', 'WMSYS')) LOOP
9 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || I.TABLE_NAME INTO V_RESULT;
10 IF V_RESULT != 0 THEN
11 BEGIN
12 V_CNT := V_CNT + 1;
13 EXECUTE IMMEDIATE 'DELETE ' || I.TABLE_NAME;
14 V_DEL := V_DEL + 1;
15 EXCEPTION
16 WHEN OTHERS THEN
17 NULL;
18 END;
19 END IF;
20 END LOOP;
21 V_LOOP := V_LOOP + 1;
22 DBMS_OUTPUT.PUT_LINE(V_LOOP || ':' || ' COUNT ' || V_CNT || ', DEL ' || V_DEL);
23 IF V_CNT != V_DEL THEN
24 V_CNT := 0;
25 V_DEL := 0;
26 GOTO LABLE_FOR_LOOP;
27 END IF;
28 END;
29 /
1: COUNT 14, DEL 14

PL/SQL 过程已成功完成。

SQL> COMMIT;

提交完成。

SQL> BEGIN
2 FOR I IN (SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DROP CONSTRAINT ' || CONSTR
AINT_NAME DROP_CONS
3 FROM DBA_CONSTRAINTS
4 WHERE OWNER = 'SYSTEM'
5 AND CONSTRAINT_NAME NOT LIKE 'SYS%'
6 AND CONSTRAINT_TYPE = 'C')
7 LOOP
8
9 EXECUTE IMMEDIATE I.DROP_CONS;
10 END LOOP;
11 END;
12 /

PL/SQL 过程已成功完成。

导入后发现ORA错误还有47个，IMP错误252个。

通过检查发现目前出现的错误中，有很多是由于导入操作时，OBJECT ID不一致造成的。解决这个错误有三种方法。

一、通过在目标库中提前安装导入对象的OID创建对象，然后再导入；

二、通过修改DMP文件中的OID标识，不过这种办法只对本库中已经存在的对象有效，而且可能会破坏dmp文件；

三、使用IMP的参数TOID_NOVALIDATE，使Oracle在执行导入的时候跳过对OID的判断。

相比较之下，第三种方法的工作量更小一些，于是尝试在导入的时候增加TOID_NOVALIDATE参数：

imp "sys as sysdba" file=testmv_full.dmp full=y buffer=20480000 ignore=y log=testmv_full.log toid_novalidate=(sys.aq$_jms_userproparray, sys.aq$_jms_text_message, system.repcat$_object_null_vector, mdsys.sdo_ordinate_array, mdsys.sdo_elem_info_array, mdsys.sdo_geometry, ordsys.orddoc, ordsys.ordaudio, ordsys.ordvideo, ordsys.ordimagesignature, ordsys.ordimage)

通过这种方法导入后，错误进一步减少，ORA错误35个，IMP错误247个。

但是观察日志发现，最后8个TYPE使用了TOID_NOVALIDATE参数仍然报错。前面几个TYPE使用TOID_NOVALIDATE参数后已经生效了，看来TOID_NOVALIDATE参数是有效的。再观察错误信息：

IMP-00060: 警告: 跳过表 "OE"."WAREHOUSES"，因为对象类型 "MDSYS"."SDO_ORDINATE_ARRAY" 不存在或具有不同的标识符

看来问题的原因是MDSYS.SDO_ORDINATE_ARRAY类型还不存在。

这里需要按照第一种方法，把对象先建立上去。首先在导出数据库查询对象的OID：

SQL> SELECT TYPE_NAME, TYPE_OID FROM DBA_TYPES
2 WHERE OWNER = 'MDSYS' AND TYPE_NAME IN ('SDO_ORDINATE_ARRAY', 'SDO_ELEM_INFO_ARRAY', 'SDO_GEOMETRY');

TYPE_NAME TYPE_OID
------------------------------ --------------------------------
SDO_ORDINATE_ARRAY 3796C48378FC407AE03400400B407D5F
SDO_ELEM_INFO_ARRAY 3796C48378FD407AE03400400B407D5F
SDO_GEOMETRY 3796C48378FE407AE03400400B407D5F

然后根据这个OID把对象建立在目标数据库上：

SQL> CREATE OR REPLACE TYPE MDSYS.SDO_ORDINATE_ARRAY OID '3796C48378FC407AE03400400B407D5F'
2 AS VARRAY(1048576) OF NUMBER;
3 /

类型已创建。

SQL> CREATE OR REPLACE TYPE MDSYS.SDO_ELEM_INFO_ARRAY OID '3796C48378FD407AE03400400B407D5F'
2 AS VARRAY (1048576) of NUMBER;
3 /

类型已创建。

SQL> CREATE OR REPLACE TYPE MDSYS.SDO_POINT_TYPE AS OBJECT (X NUMBER, Y NUMBER, Z NUMBER);
2 /

类型已创建。

SQL> CREATE OR REPLACE TYPE MDSYS.SDO_GEOMETRY OID '3796C48378FE407AE03400400B407D5F' AS OBJECT (
2 SDO_GTYPE NUMBER,
3 SDO_SRID NUMBER,
4 SDO_POINT SDO_POINT_TYPE,
5 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
6 SDO_ORDINATES SDO_ORDINATE_ARRAY,
7 MEMBER FUNCTION GET_GTYPE
8 RETURN NUMBER DETERMINISTIC,
9 MEMBER FUNCTION GET_DIMS
10 RETURN NUMBER DETERMINISTIC,
11 MEMBER FUNCTION GET_LRS_DIM
12 RETURN NUMBER DETERMINISTIC);
13 /

类型已创建。

SQL> CREATE OR REPLACE TYPE BODY MDSYS.SDO_GEOMETRY IS
2 MEMBER FUNCTION GET_GTYPE
3 RETURN NUMBER IS
4 gtype integer;
5 BEGIN
6 gtype := sdo_gtype MOD 100;
7 return gtype;
8 END;
9 MEMBER FUNCTION GET_DIMS
10 RETURN NUMBER IS
11 dims INTEGER;
12 BEGIN
13 dims := sdo_gtype/1000;
14 return dims;
15 END;
16
17 MEMBER FUNCTION GET_LRS_DIM
18 RETURN NUMBER IS
19 lrs_dim integer;
20 BEGIN
21 lrs_dim := sdo_gtype/100;
22 lrs_dim := lrs_dim MOD 10;
23 return lrs_dim;
24 END;
25 END;
26 /

类型主体已创建。

SQL> GRANT EXECUTE ON MDSYS.SDO_ORDINATE_ARRAY TO PUBLIC WITH GRANT OPTION;

授权成功。

SQL> GRANT EXECUTE ON MDSYS.SDO_ELEM_INFO_ARRAY TO PUBLIC WITH GRANT OPTION;

授权成功。

SQL> GRANT EXECUTE ON MDSYS.SDO_POINT_TYPE TO PUBLIC WITH GRANT OPTION;

授权成功。

SQL> GRANT EXECUTE ON MDSYS.SDO_GEOMETRY TO PUBLIC WITH GRANT OPTION;

授权成功。

再次执行导入操作，ORA错误还有24个，IMP错误还有213个。

下面ORDSYS用户的对象面临同样的问题，但是ORDSYS用户的TYPE数据比较多，而且创建相对比较复杂，于是考虑在删除用户的时候不删除ORDSYS用户，只将ORDSYS用户表中的数据删除。修改前面删除用户和用户数据的脚本：

SQL> DECLARE
2 TYPE T_VARCHAR_TAB IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 V_DROP_STR T_VARCHAR_TAB;
4 V_CREATE_STR T_VARCHAR_TAB;
5 BEGIN
6 SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) BULK COLLECT INTO V_CREATE_STR
7 FROM DBA_USERS
8 WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'WMSYS', 'ORDSYS');
9 SELECT 'DROP USER ' || USERNAME || ' CASCADE' BULK COLLECT INTO V_DROP_STR
10 FROM DBA_USERS
11 WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'WMSYS', 'ORDSYS');
12 FOR I IN 1..V_DROP_STR.COUNT LOOP
13 EXECUTE IMMEDIATE V_DROP_STR(I);
14 END LOOP;
15 FOR I IN 1..V_CREATE_STR.COUNT LOOP
16 EXECUTE IMMEDIATE V_CREATE_STR(I);
17 END LOOP;
18 END;
19 /

PL/SQL 过程已成功完成。

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_CNT NUMBER := 0;
3 V_DEL NUMBER := 0;
4 V_LOOP NUMBER := 0;
5 V_RESULT NUMBER := 0;
6 BEGIN
7 <<LABLE_FOR_LOOP>>
8 FOR I IN (SELECT OWNER || '.' || TABLE_NAME TABLE_NAME FROM DBA_TABLES WHERE OWNER IN ('SYSTEM', 'WMSYS', 'ORDSYS')) LOOP
9 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || I.TABLE_NAME INTO V_RESULT;
10 IF V_RESULT != 0 THEN
11 BEGIN
12 V_CNT := V_CNT + 1;
13 EXECUTE IMMEDIATE 'DELETE ' || I.TABLE_NAME;
14 V_DEL := V_DEL + 1;
15 EXCEPTION
16 WHEN OTHERS THEN
17 NULL;
18 END;
19 END IF;
20 END LOOP;
21 V_LOOP := V_LOOP + 1;
22 DBMS_OUTPUT.PUT_LINE(V_LOOP || ':' || ' COUNT ' || V_CNT || ', DEL ' || V_DEL);
23 IF V_CNT != V_DEL THEN
24 V_CNT := 0;
25 V_DEL := 0;
26 GOTO LABLE_FOR_LOOP;
27 END IF;
28 END;
29 /
1: COUNT 16, DEL 16

PL/SQL 过程已成功完成。

SQL> COMMIT;

提交完成。

重新执行导入后，所有和TYPE相关的错误消失，至此，还剩ORA错误21个，IMP错误202个。

检查日志文件发现，大部分IMP错误信息来自OLAPSYS用户的创建视图过程。

检查第一个发出告警的视图，发现创建脚本包括了SYS用户的表。我们知道，使用全库导入Oracle会自动忽略SYS或者SYSTEM方案下的对象权限。这个问题的具体描述参见：http://yangtingkun.itpub.net/post/468/220918

而在导入之前删除了OLAPSYS用户，因此OLAPSYS用户不在拥有SYS对象的访问权限，而导入又忽略了这些权限，于是产生了错误。

因此，尝试重建用户时保留用户的权限。

SQL> DECLARE
2 TYPE T_VARCHAR_TAB IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 V_PRIVS_STR T_VARCHAR_TAB;
4 V_ROLE_STR T_VARCHAR_TAB;
5 V_DROP_STR VARCHAR2(32767);
6 V_CREATE_STR VARCHAR2(32767);
7 BEGIN
8 FOR I IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'WMSYS', 'ORDSYS')) LOOP
9 SELECT DBMS_METADATA.GET_DDL('USER', I.USERNAME) INTO V_CREATE_STR FROM DUAL;
10
11 SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE
12 BULK COLLECT INTO V_PRIVS_STR
13 FROM DBA_TAB_PRIVS WHERE GRANTEE = I.USERNAME;
14
15 SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE
16 BULK COLLECT INTO V_ROLE_STR
17 FROM DBA_ROLE_PRIVS WHERE GRANTEE = I.USERNAME;
18
19 SELECT 'DROP USER ' || I.USERNAME || ' CASCADE' INTO V_DROP_STR FROM DUAL;
20
21 EXECUTE IMMEDIATE V_DROP_STR;
22 EXECUTE IMMEDIATE V_CREATE_STR;
23 FOR I IN 1..V_PRIVS_STR.COUNT LOOP
24 BEGIN
25 EXECUTE IMMEDIATE V_PRIVS_STR(I);
26 EXCEPTION
27 WHEN OTHERS THEN
28 NULL;
29 END;
30 END LOOP;
31 FOR I IN 1..V_ROLE_STR.COUNT LOOP
32 EXECUTE IMMEDIATE V_ROLE_STR(I);
33 END LOOP;
34 END LOOP;
35 END;
36 /

PL/SQL 过程已成功完成。

执行导入操作后发现，OLAPSYS用户的错误仍然存在，但是错误数目已经减少。IMP错误减少到了127个。

仔细检查OLAPSYS用户的错误，发现是由于参考了OLAPSYS本身的包造成的，由于Oracle导入表在导入包之前，因此导致了这个错误。在导入结束后，这些发生告警的视图都是可以访问的。

原则上没有必要再去追究这些告警，不过本文档的目的就是尽量的加上错误和警告，因此这里选择了手工提前创建包的方法：

SQL> ALTER SESSION SET CURRENT_SCHEMA=OLAPSYS;

会话已更改。

SQL> @?/cwmlite/admin/oneputlp.pls

程序包已创建。

SQL> @?/cwmlite/admin/cwm2asec.pls

程序包已创建。

没有错误。
SQL> ALTER SESSION SET CURRENT_SCHEMA=SYS;

会话已更改。

然后执行导入操作，效果很令人惊喜，ORA错误虽然没有变仍然是21个，但是IMP错误已经减少到了17个。

OLAPSYS用户已经没有在报任何的错误和告警信息了。

WKSYS用户下的错误和OLAPSYS的类型，加上下面的代码后错误消失：

SQL> CREATE OR REPLACE FUNCTION WKSYS.WK$USER
2 RETURN VARCHAR2
3 AS
4 BEGIN
5 return nvl(sys_context('WK$CONTEXT', 'USER'),
6 sys_context('USERENV', 'SESSION_USER'));
7 END;
8 /

函数已创建。

SQL> CREATE OR REPLACE FUNCTION WKSYS.WK$USERID
2 RETURN NUMBER
3 AS
4 FUNCTION GET_DATABASE_USERID RETURN VARCHAR2 AS
5 ID NUMBER;
6 BEGIN
7 SELECT USER# INTO ID FROM SYS.USER$
8 WHERE NAME = SYS_CONTEXT('USERENV','SESSION_USER');
9 RETURN ID;
10 END;
11 BEGIN
12 RETURN NVL(TO_NUMBER(SYS_CONTEXT('WK$CONTEXT','USERID')),
13 GET_DATABASE_USERID);
14 END;
15 /

函数已创建。

SQL> CREATE TABLE WKSYS.WK$SYS_PRIV (
2 SP_USERID NUMBER,
3 SP_USER VARCHAR2(100),
4 SP_OPTIONS VARCHAR2(10),
5 PRIMARY KEY (SP_USERID, SP_USER) USING INDEX TABLESPACE DRSYS
6 ) TABLESPACE DRSYS;

表已创建。

SQL> CREATE OR REPLACE FUNCTION WKSYS.WK$IS_ADMIN(
2 WITH_OPTION IN VARCHAR2 DEFAULT NULL
3 )
4 RETURN NUMBER
5 AS
6 CUSER VARCHAR2(200) := WK$USER;
7 CUID NUMBER := WK$USERID;
8 BEGIN
9 IF (CUID >= 0 AND CUSER IN ('WKSYS', 'SYS')) THEN
10 IF (WITH_OPTION IS NULL OR INSTR('G', WITH_OPTION) > 0) THEN
11 RETURN 1;
12 END IF;
13 ELSIF (CUID = -2 AND CUSER IN ('IAS_ADMIN')) THEN
14 IF (WITH_OPTION IS NULL OR INSTR('G', WITH_OPTION) > 0) THEN
15 RETURN 1;
16 END IF;
17 ELSE
18 FOR C IN (SELECT SP_USERID FROM WK$SYS_PRIV
19 WHERE SP_USER = CUSER AND SP_USERID = CUID AND
20 (WITH_OPTION IS NULL OR
21 INSTR(SP_OPTIONS, WITH_OPTION) > 0))
22 LOOP
23 RETURN 1;
24 END LOOP;
25 END IF;
26 RETURN 0;
27 END;
28 /

函数已创建。

至此，ORA错误还是21个。IMP错误还剩15个。

经过上面的诸多处理后，还剩下ORA错误21个，IMP错误15个。错误越剩越少，解决起来也越来越困难。

观察错误日志，发现OE用户下有一个视图创建出现了告警，本以为会和OLAPSYS或WKSYS用户的错误相同，结果检查发现，既不是权限的问题，也不是视图中用到的函数还没有导入的问题。

排除了其他问题后，基本上确定了问题和UNDER VIEW有关。可能是IMP工具在处理UNDER VIEW时有些问题。看来要想解决这个问题，最好的办法时删除用户时保留OE用户，这样避免IMP工具导入OC_CORPORATE_CUSTOMERS视图。

但是，保留了OE用户，会导致主键冲突的问题。于是，修改删除用户脚本的同时在删除用户数据的脚本中加入了OE用户，但是导入的时候仍然存在问题，由于OE用户的表存在着主外键关系，而且在导入的时候，这个约束已经在起作用了，所以导入很多的数据无法插入。

于是，只好通过删除OE用户下表的方法来避免这个问题。解决完表的问题又出现了新的问题，由于OE用户下的对象没有删除，在导入的时候，Oracle检查到了对象具有不同的OID。于是采用前面处理ORDSYS等用户对象错误的方法，为OE的CATEGORY_TYP已源数据库的OID重建，并编译失效的对象。

进行完这些处理后，重新导入，这时候OE用户的导入告警也消失了，整个IMP操作还剩下ORA错误21个，IMP错误14个。

检查这35个错误可以发现，这35个错误是7个队列表在进行导入的后期处理时产生的。

通过查询METALINK发现，这些错误属于Oracle的bug。Oracle9208以下版本都会受到影响。如果想要解决这个问题，需要把Oracle版本升级到9208，单独的补丁居然只有AIX平台下有，且要求Oracle rdbms版本必须是9207。

既然是Oracle的bug，这里就不费劲去解决了。

下面简单总结一下：全库导入操作带来的问题很多，其中有些错误必须要给予足够的重视，比较SYS对象的权限问题，以及XMLTYPE无法显示的问题，这些问题不解决，可能导致导入的失败，或者导入后数据库权限与导出数据库不一致。

对于其他部分错误，其实大部分可以简单忽略掉，或者仅对需要用到的用户额外关注一下，不过为了完整起见，这里还是将这几篇文章中，全库导入前的所有操作根据执行顺序完整的列出来，并对其中部分内容进行了简化：

SQL> DECLARE
2 TYPE T_VARCHAR_TAB IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 V_PRIVS_STR T_VARCHAR_TAB;
4 V_ROLE_STR T_VARCHAR_TAB;
5 V_DROP_STR VARCHAR2(32767);
6 V_CREATE_STR VARCHAR2(32767);
7 BEGIN
8 FOR I IN (SELECT USERNAME FROM DBA_USERS
9 WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'WMSYS', 'ORDSYS', 'OE')) LOOP
10
11 SELECT DBMS_METADATA.GET_DDL('USER', I.USERNAME) INTO V_CREATE_STR FROM DUAL;
12
13 SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE
14 BULK COLLECT INTO V_PRIVS_STR
15 FROM DBA_TAB_PRIVS WHERE GRANTEE = I.USERNAME;
16
17 SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE
18 BULK COLLECT INTO V_ROLE_STR
19 FROM DBA_ROLE_PRIVS WHERE GRANTEE = I.USERNAME;
20
21 SELECT 'DROP USER ' || I.USERNAME || ' CASCADE' INTO V_DROP_STR FROM DUAL;
22
23 EXECUTE IMMEDIATE V_DROP_STR;
24 EXECUTE IMMEDIATE V_CREATE_STR;
25 FOR I IN 1..V_PRIVS_STR.COUNT LOOP
26 BEGIN
27 EXECUTE IMMEDIATE V_PRIVS_STR(I);
28 EXCEPTION
29 WHEN OTHERS THEN
30 NULL;
31 END;
32 END LOOP;
33 FOR I IN 1..V_ROLE_STR.COUNT LOOP
34 EXECUTE IMMEDIATE V_ROLE_STR(I);
35 END LOOP;
36 END LOOP;
37 END;
38 /

PL/SQL 过程已成功完成。

SQL> DROP PUBLIC SYNONYM GENREMOTETASKINTERRUPTEDEXCEPT;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENREMOTEOBJECTCLOSEDEXCEPTION;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCONNECTIONPARAMETERINFOSEQU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENREMOTEAUTHENTICATIONEXCEPTI;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENREMOTEAPIVERSIONMISMATCHEXC;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENMETADATAPROPERTYVALUESUNION;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENMETADATAPROPERTYBAGSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCONNECTIONPARAMETERINFOSTRU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCONNECTIONPARAMETERTYPEENUM;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENREMOTESPECIFICATIONUPDATENE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCOMPOUNDCURSORBLOCKSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENPARENTSTARTENDBLOCKSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENINVALIDINDEXSPECIFICATIONEX;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENINCLUDEDDEPENDENCYBLOCKSTRU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENDEPENDENCYBLOCKSEQUENCESEQU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCURSORSPECIFIERSEQUENCESEQU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENRECURSIVEJOINDEFINITIONSTRU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM GENCONSTANTLISTDEFINITIONSTRUC;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLREMOTETASKINTERRUPTEDEXCEPT;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLMETADATAPROPERTYVALUESUNION;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLMETADATAPROPERTYBAGSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLREMOTEAUTHENTICATIONEXCEPTI;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLREMOTEAPIVERSIONMISMATCHEXC;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLCONNECTIONPARAMETERINFOSEQU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLCONNECTIONPARAMETERTYPEENUM;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLCONNECTIONPARAMETERINFOSTRU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLPARENTSTARTENDBLOCKSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLINCLUDEDDEPENDENCYBLOCKSTRU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLREMOTESPECIFICATIONUPDATENE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLINVALIDINDEXSPECIFICATIONEX;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLDEPENDENCYBLOCKSEQUENCESEQU;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLCOMPOUNDCURSORBLOCKSEQUENCE;

同义词已删除。

SQL> DROP PUBLIC SYNONYM SQLCONSTANTLISTDEFINITIONSTRUC;

同义词已删除。

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_CNT NUMBER := 0;
3 V_DEL NUMBER := 0;
4 V_LOOP NUMBER := 0;
5 V_RESULT NUMBER := 0;
6 BEGIN
7 <<LABLE_FOR_LOOP>>
8 FOR I IN (SELECT OWNER || '.' || TABLE_NAME TABLE_NAME FROM DBA_TABLES
9 WHERE OWNER IN ('SYSTEM', 'WMSYS', 'ORDSYS', 'OE')) LOOP
10 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || I.TABLE_NAME INTO V_RESULT;
11 IF V_RESULT != 0 THEN
12 BEGIN
13 V_CNT := V_CNT + 1;
14 EXECUTE IMMEDIATE 'DELETE ' || I.TABLE_NAME;
15 V_DEL := V_DEL + 1;
16 EXCEPTION
17 WHEN OTHERS THEN
18 NULL;
19 END;
20 END IF;
21 END LOOP;
22 V_LOOP := V_LOOP + 1;
23 DBMS_OUTPUT.PUT_LINE(V_LOOP || ':' || ' COUNT ' || V_CNT || ', DEL ' || V_DEL);
24 IF V_CNT != V_DEL THEN
25 V_CNT := 0;
26 V_DEL := 0;
27 GOTO LABLE_FOR_LOOP;
28 END IF;
29 END;
30 /
1: COUNT 25, DEL 22
2: COUNT 3, DEL 3

PL/SQL 过程已成功完成。

SQL> COMMIT;

提交完成。

PL/SQL 过程已成功完成。

SQL> CREATE OR REPLACE TYPE MDSYS.SDO_ORDINATE_ARRAY OID '3796C48378FC407AE03400400B407D5F'
2 AS VARRAY(1048576) OF NUMBER;
3 /

类型已创建。

SQL> CREATE OR REPLACE TYPE MDSYS.SDO_ELEM_INFO_ARRAY OID '3796C48378FD407AE03400400B407D5F'
2 AS VARRAY (1048576) of NUMBER;
3 /

类型已创建。

SQL> CREATE OR REPLACE TYPE MDSYS.SDO_POINT_TYPE AS OBJECT (X NUMBER, Y NUMBER, Z NUMBER);
2 /

类型已创建。

SQL> GRANT EXECUTE ON MDSYS.SDO_ORDINATE_ARRAY TO PUBLIC WITH GRANT OPTION;

授权成功。

SQL> GRANT EXECUTE ON MDSYS.SDO_ELEM_INFO_ARRAY TO PUBLIC WITH GRANT OPTION;

授权成功。

SQL> GRANT EXECUTE ON MDSYS.SDO_POINT_TYPE TO PUBLIC WITH GRANT OPTION;

授权成功。

SQL> GRANT EXECUTE ON MDSYS.SDO_GEOMETRY TO PUBLIC WITH GRANT OPTION;

授权成功。

SQL> CREATE OR REPLACE FUNCTION WKSYS.WK$USER
2 RETURN VARCHAR2
3 AS
4 BEGIN
5 return nvl(sys_context('WK$CONTEXT', 'USER'),
6 sys_context('USERENV', 'SESSION_USER'));
7 END;
8 /

函数已创建。

SQL> CREATE TABLE WKSYS.WK$SYS_PRIV (
2 SP_USERID NUMBER,
3 SP_USER VARCHAR2(100),
4 SP_OPTIONS VARCHAR2(10),
5 PRIMARY KEY (SP_USERID, SP_USER) USING INDEX TABLESPACE DRSYS
6 ) TABLESPACE DRSYS;

表已创建。

函数已创建。

SQL> DECLARE
2 V_COUNT NUMBER;
3 V_RESULT NUMBER;
4 BEGIN
5 <<LABLE_FOR_LOOP>>
6 SELECT COUNT(*) INTO V_COUNT FROM DBA_OBJECTS WHERE OWNER = 'OE' AND OBJECT_TYPE = 'TABLE';
7 V_RESULT := V_COUNT;
8 FOR I IN (SELECT 'DROP TABLE OE.' || OBJECT_NAME DROP_STR FROM DBA_OBJECTS
9 WHERE OWNER = 'OE' AND OBJECT_TYPE = 'TABLE') LOOP
10 BEGIN
11 EXECUTE IMMEDIATE I.DROP_STR;
12 V_COUNT := V_COUNT - 1;
13 EXCEPTION
14 WHEN OTHERS THEN
15 NULL;
16 END;
17 END LOOP;
18 DBMS_OUTPUT.PUT_LINE('REMAIN :' || V_RESULT);
19 IF V_COUNT != V_RESULT THEN
20 GOTO LABLE_FOR_LOOP;
21 END IF;
22 END;
23 /
REMAIN :10
REMAIN :2
REMAIN :1
REMAIN :0

PL/SQL 过程已成功完成。

SQL> DROP TYPE OE.CATEGORY_TYP FORCE;

类型已删除。

SQL> CREATE OR REPLACE TYPE OE.CATEGORY_TYP OID '82A4AF6A4CDC656DE034080020E0EE3D'
2 AS OBJECT
3 ( CATEGORY_NAME VARCHAR2(50)
4 , CATEGORY_DESCRIPTION VARCHAR2(1000)
5 , CATEGORY_ID NUMBER(2)
6 , NOT INSTANTIABLE
7 MEMBER FUNCTION CATEGORY_DESCRIBE RETURN VARCHAR2
8 )
9 NOT INSTANTIABLE NOT FINAL
10 ALTER TYPE OE.CATEGORY_TYP
11 ADD ATTRIBUTE (PARENT_CATEGORY_ID NUMBER(2)) CASCADE;
12 /

类型已创建。

SQL> ALTER TYPE OE.SUBCATEGORY_REF_LIST_TYP COMPILE;

类型已变更。

SQL> ALTER TYPE OE.LEAF_CATEGORY_TYP COMPILE;

类型已变更。

SQL> ALTER TYPE OE.COMPOSITE_CATEGORY_TYP COMPILE;

类型已变更。

SQL> ALTER TYPE OE.CATALOG_TYP COMPILE;

类型已变更。

SQL> ALTER SESSION SET CURRENT_SCHEMA=OLAPSYS;

会话已更改。

SQL> @?/cwmlite/admin/oneputlp.pls

程序包已创建。

SQL> @?/cwmlite/admin/cwm2asec.pls

程序包已创建。

没有错误。
SQL> ALTER SESSION SET CURRENT_SCHEMA=SYS;

会话已更改。

$ imp "sys as sysdba" file=testmv_full.dmp full=y buffer=20480000 ignore=y log=testmv_full.log toid_novalidate=(sys.aq$_jms_userproparray, sys.aq$_jms_text_message, system.repcat$_object_null_vector, ordsys.orddoc, ordsys.ordaudio, ordsys.ordvideo, ordsys.ordimagesignature, ordsys.ordimage)

执行完导入任务后，检查日志信息，错误信息仅包括由于Oracle自身bug造成的35个警告，比原来总共的3400多个错误缩小了近100倍。

最后声明一下，所有操作均针对Oracle920数据库，如果数据库为其他版本，不要效仿这些操作，不过解决问题的思路是一致的。