AI 助理
备案控制台
开发者社区 数据库 文章 正文

IMP-00041: Warning: object created with compilation warnings

2016-02-29 4667
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： <p><br></p> <p><strong style="background-color:rgb(255,255,0)"><span style="font-size:14px; color:#ff0000">IMP-00041: Warning: object created with compilation warnings</span></strong><br></p> <p


IMP-00041: Warning: object created with compilation warnings



IMP- 00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "QBJMES"."SMY_FILE6"                          ("SMYSLIP","
 ""SMYDESC","SMYAUNO","SMYMXNO","SMYSYS","SMYKIND","SMYAPR","SMYATSG","SMYSIG"
 "N","SMYDAYS","SMYPRIT","SMYPRINT","SMYDMY1","SMYDMY2","SMYDMY3","SMYDMY4",""
 "SMYDMY5","SMYWARE","SMY51","SMY52","SMY53","SMY54","SMY55","SMY56","SMY57","
 ""SMY58","SMY59","SMY60","SMYMEMO1","SMYMEMO2","SMYMEMO3","SMYACTI","SMYUSER"
 "","SMYGRUP","SMYMODU","SMYDATE","SMY61","SMY62") AS "
 "select "SMYSLIP","SMYDESC","SMYAUNO","SMYMXNO","SMYSYS","SMYKIND","SMYAPR","
 ""SMYATSG","SMYSIGN","SMYDAYS","SMYPRIT","SMYPRINT","SMYDMY1","SMYDMY2","SMY"
 "DMY3","SMYDMY4","SMYDMY5","SMYWARE","SMY51","SMY52","SMY53","SMY54","SMY55""
 ","SMY56","SMY57","SMY58","SMY59","SMY60","SMYMEMO1","SMYMEMO2","SMYMEMO3",""
 "SMYACTI","SMYUSER","SMYGRUP","SMYMODU","SMYDATE","SMY61","SMY62" from smy_f"
 "ile@ds6_link"
IMP- 00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "QBJMES"."SMY_FILE4"                          ("SMYSLIP","
 ""SMYDESC","SMYAUNO","SMYMXNO","SMYSYS","SMYKIND","SMYAPR","SMYATSG","SMYSIG"
 "N","SMYDAYS","SMYPRIT","SMYPRINT","SMYDMY1","SMYDMY2","SMYDMY3","SMYDMY4",""
 "SMYDMY5","SMYWARE","SMY51","SMY52","SMY53","SMY54","SMY55","SMY56","SMY57","
 ""SMY58","SMY59","SMY60","SMYMEMO1","SMYMEMO2","SMYMEMO3","SMYACTI","SMYUSER"
 "","SMYGRUP","SMYMODU","SMYDATE","SMY61","SMY62") AS "
 "select "SMYSLIP","SMYDESC","SMYAUNO","SMYMXNO","SMYSYS","SMYKIND","SMYAPR","
 ""SMYATSG","SMYSIGN","SMYDAYS","SMYPRIT","SMYPRINT","SMYDMY1","SMYDMY2","SMY"
 "DMY3","SMYDMY4","SMYDMY5","SMYWARE","SMY51","SMY52","SMY53","SMY54","SMY55""
 ","SMY56","SMY57","SMY58","SMY59","SMY60","SMYMEMO1","SMYMEMO2","SMYMEMO3",""
 "SMYACTI","SMYUSER","SMYGRUP","SMYMODU","SMYDATE","SMY61","SMY62" from smy_f"
 "ile@ds4_link"
IMP- 00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "QBJMES"."SFP_FILE6"                          ("SFP01","S"
 "FP02","SFP03","SFP04","SFP05","SFP06","SFP07","SFP08","SFP09","SFP10","SFPU"
 "SER","SFPGRUP","SFPMODU","SFPDATE","SFP11","SFPCONF") AS "
 "select "SFP01","SFP02","SFP03","SFP04","SFP05","SFP06","SFP07","SFP08","SFP"
 "09","SFP10","SFPUSER","SFPGRUP","SFPMODU","SFPDATE","SFP11","SFPCONF" from "
 "sfp_file@ds6_link"
IMP- 00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "QBJMES"."SFP_FILE4"                          ("SFP01","S"
 "FP02","SFP03","SFP04","SFP05","SFP06","SFP07","SFP08","SFP09","SFP10","SFPU"
 "SER","SFPGRUP","SFPMODU","SFPDATE","SFP11","SFPCONF") AS "
 "select "SFP01","SFP02","SFP03","SFP04","SFP05","SFP06","SFP07","SFP08","SFP"
 "09","SFP10","SFPUSER","SFPGRUP","SFPMODU","SFPDATE","SFP11","SFPCONF" from "
 "sfp_file@ds4_link"
IMP- 00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "QBJMES"."GEM_FILE6"                          ("GEM01","G"
 "EM02","GEM03","GEM04","GEM05","GEM06","GEM07","GEM08","GEMACTI","GEMUSER",""
 "GEMGRUP","GEMMODU","GEMDATE") AS "
 "select "GEM01","GEM02","GEM03","GEM04","GEM05","GEM06","GEM07","GEM08","GEM"
 "ACTI","GEMUSER","GEMGRUP","GEMMODU","GEMDATE" from gem_file@ds6_link"
IMP- 00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "QBJMES"."GEM_FILE4"                          ("GEM01","G"
 "EM02","GEM03","GEM04","GEM05","GEM06","GEM07","GEM08","GEMACTI","GEMUSER",""
 "GEMGRUP","GEMMODU","GEMDATE") AS "
 "select "GEM01","GEM02","GEM03","GEM04","GEM05","GEM06","GEM07","GEM08","GEM"
 "ACTI","GEMUSER","GEMGRUP","GEMMODU","GEMDATE" from gem_file@ds4_link"
IMP- 00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "QBJMES"."AZN_FILE6"                          ("AZN01","A"
 "ZN02","AZN03","AZN04","AZN05") AS "
 "select "AZN01","AZN02","AZN03","AZN04","AZN05" from azn_file@ds6_link"
IMP- 00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "QBJMES"."AZN_FILE4"                          ("AZN01","A"
 "ZN02","AZN03","AZN04","AZN05") AS "
 "select "AZN01","AZN02","AZN03","AZN04","AZN05" from azn_file@ds4_link"
Import terminated successfully with warnings.


问题现象是这样的，在IMP数据的过程中出现“ IMP-00041: Warning: object created with compilation warnings ”错误，因这个错误导致视图导入后无法使用。经分析，发现此问题与“授权信息丢失”有关。
为避免朋友们走弯路，我模拟再现一下这个问题，同时给出一个分析和解决问题的思路。

1.创建两个用户sec1和sec2
sys@ora10g> create user sec1 identified by sec1 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource to sec1;

Grant succeeded.

sys@ora10g> create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource to sec2;

Grant succeeded.

2.在第一个用户sec1中创建表T_SEC1
sys@ora10g> conn sec1/sec1
Connected.
sec1@ora10g> create table t_sec1 (x int);

Table created.

sec1@ora10g> insert into t_sec1 values (1);

1 row created.

3.在第二个用户sec2中创建表T_SEC2
sec1@ora10g> conn sec2/sec2
Connected.
sec2@ora10g> create table t_sec2 (x int);

Table created.

sec2@ora10g> insert into t_sec2 values (2);

1 row created.

4.在sec2中创建sec1用户中t_sec1表的同名
sec2@ora10g> conn / as sysdba
Connected.
sys@ora10g> create synonym sec2.syn_t_sec1 for sec1.t_sec1;

Synonym created.

sys@ora10g> grant insert,delete,update,select on sec1.t_sec1 to sec2;

Grant succeeded.

5.在sec2用户下创建视图v_sec2
该视图同时使用到刚刚在sec2用户下创建的同名SYN_T_SEC1和表T_SEC2。
sec2@ora10g> conn / as sysdba
Connected.
sys@ora10g>
sys@ora10g> grant create view to sec2;

Grant succeeded.

sys@ora10g> conn sec2/sec2
Connected.
sec2@ora10g> create view v_sec2 as select SYN_T_SEC1.x sec1_x, T_SEC2.x sec2_x from SYN_T_SEC1,T_SEC2;

View created.

6.分别看一下各个用户下的数据库对象
sec2@ora10g> conn sec1/sec1
Connected.
sec1@ora10g> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
T_SEC1                         TABLE


sec2@ora10g> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
T_SEC2                         TABLE
SYN_T_SEC1                     SYNONYM
V_SEC2                         VIEW

7.我们这里分别生成sec1和sec2用户的备份
ora10g@secDB1 /exp$ exp sec1/sec1 file=sec1.dmp log=sec1.log

Export: Release 10.2.0.3.0 - Production on Fri Feb 26 03:01:40 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC1
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC1
About to export SEC1's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC1's tables via Conventional Path ...
. . exporting table                         T_SEC1          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

ora10g@secDB1 /exp$ exp sec2/sec2 file=sec2.dmp log=sec2.log

Export: Release 10.2.0.3.0 - Production on Fri Feb 26 03:01:56 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC2
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC2
About to export SEC2's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC2's tables via Conventional Path ...
. . exporting table                         T_SEC2          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

8.将备份文件发送到待导入的服务器secDB2
ora10g@secDB1 /exp$ scp sec1.dmp sec2.dmp 172.17.193.201:/imp
oracle@172.17.193.201's password:
sec1.dmp  100%   16KB  16.0KB/s   00:00
sec2.dmp  100%   16KB  16.0KB/s   00:00

9.在secDB2服务器上创建同样的用户，并进行导入测试
1）创建用户并授权
sys@ora10g> create user sec1 identified by sec1 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource to sec1;

Grant succeeded.

sys@ora10g> create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource,dba to sec2;

Grant succeeded.

2）导入测试
（1）先导入sec1用户
ora10g@secDB2 /imp$ imp sec1/sec1 file=sec1.dmp log=sec1.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 11:58:09 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC1's objects into SEC1
. . importing table                       "T_SEC1"          1 rows imported
Import terminated successfully without warnings.

（2）导入sec2用户
 ora10g @secDB2 /imp$ imp sec2/sec2 file=sec2.dmp log=sec2.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:14:59 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC2's objects into SEC2
. . importing table                       "T_SEC2"          1 rows imported
Import terminated successfully without warnings.

此时是导入成功的！演示还未结束，请继续。

（3）假如此时删除sec2用户（删除的目的可能有很多，比如之前授予的权限不足等），对其进行重新创建后再完成数据导入
sys@ ora10g > drop user sec2 cascade;

User dropped.

sys@ ora10g > create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ ora10g > grant connect,resource,dba to sec2;

Grant succeeded.

（4）再重新完成sec2用户的IMP导入
 ora10g @secDB2 /imp$ imp sec2/sec2 file=sec2.dmp log=sec2.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:20:48 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC2's objects into SEC2
. . importing table                       "T_SEC2"          1 rows imported
IMP-00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "SEC2"."V_SEC2"                            ("SEC1_X","SEC"
 "2_X") AS "
 "select SYN_T_SEC1.x sec1_x, T_SEC2.x sec2_x from SYN_T_SEC1,T_SEC2"
Import terminated successfully with warnings.

此时，问题出现了，此时的“IMP-00041”错误提示内容是创建的视图存在编译错误（这个错误没有太大的指导意义）。

10.验证被导入的内容是否可用
1）导入了三个对象，没有问题。
sec2@ora10g> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
SYN_T_SEC1                     SYNONYM
T_SEC2                         TABLE
V_SEC2                         VIEW

2）T_SEC2表可用
sec2@ora10g> select * from T_SEC2;

         X
----------
         2

3）SYN_T_SEC1同名可用
sec2@ora10g> select * from SYN_T_SEC1;

         X
----------
         1

4）此时视图不可用，提示存在错误。原因不详细。
sec2@ora10g> select * from V_SEC2;
select * from V_SEC2
              *
ERROR at line 1:
ORA-04063: view "SEC2.V_SEC2" has errors

5）尝试重新编译，无效。
sec2@ora10g> alter view V_SEC2 compile;

Warning: View altered with compilation errors.

6）没有具体的错误提示信息
sec2@ora10g> show errors;
No errors.

11.问题原因
根本原因在于，当删除sec2用户重新创建后，sec2用户原来具有的sec1用户下T_SEC1表授权信息丢失了。
不要着急，我们来分析一下。
在完成sec1用户导入后，其实sec1用户的dmp文件中包含的授权信息已经完成对sec2用户的授权。我们使用“show=y”选项查看一下sec1用户的dmp文件内容。
 ora10g @secDB2 /imp$ imp sec1/sec1 file=sec1.dmp log=sec1.log ignore=y full=y show=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:24:50 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC1's objects into SEC1
 "BEGIN  "
 "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
 "CURRENT_SCHEMA'), export_db_name=>'ORA10G', inst_scn=>'36699433');"
 "COMMIT; END;"
 "CREATE TABLE "T_SEC1" ("X" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 M"
 "AXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL"
 " DEFAULT) TABLESPACE "TBS_SEC_D" LOGGING NOCOMPRESS"
. . skipping table "T_SEC1"

 "GRANT DELETE ON "T_SEC1" TO "SEC2""
 "GRANT INSERT ON "T_SEC1" TO "SEC2""
 "GRANT SELECT ON "T_SEC1" TO "SEC2""
 "GRANT UPDATE ON "T_SEC1" TO "SEC2""
Import terminated successfully without warnings.

注意最后四行的授权信息。这些授权信息是在sec1用户数据导入过程中同时完成的。

12.问题处理
既然知道了问题的出处，处理就简单了。以sys用户显示的将sec1用户下t_sec1表的操作权限授予sec2用户，然后再重新对问题视图进行编译（当然，重新创建这个视图亦可）。
sec2@ora10g> conn / as sysdba
Connected.
sys@ora10g> grant insert,delete,update,select on sec1.t_sec1 to sec2;

Grant succeeded.

sys@ora10g> conn sec2/sec2
Connected.
sec2@ora10g> alter view V_SEC2 compile;

View altered.

sec2@ora10g> select * from V_SEC2;

    SEC1_X     SEC2_X
---------- ----------
         1          2

1 row selected.

OK，问题到此处理完毕。

13.另外一种导致这个问题的场景演示
还用一种可能出现这种问题的可能性，如果是按照下面的顺序在secDB2服务器上完成用户的创建和导入，一样会报上面的错误。
创建sec1用户，完成对sec1用户的导入；
创建sec2用户，完成对sec2用户的导入。

因为在完成sec1用户导入后，授权信息的授予对象sec2还不存在！

为保证信息的完整性和正确性，赘述在此。
1）删除sec1和sec2用户
sys@ ora10g > drop user sec1 cascade;

User dropped.

sys@ ora10g > drop user sec2 cascade;

User dropped.

2）创建sec1用户，完成对sec1用户的导入
sys@ ora10g > create user sec1 identified by sec1 default tablespace TBS_SEC_D;

User created.

sys@ ora10g > grant connect,resource to sec1;

Grant succeeded.

sys@ ora10g > exit

 ora10g @secDB2 /imp$ imp sec1/sec1 file=sec1.dmp log=sec1.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:47:11 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC1's objects into SEC1
. . importing table                       "T_SEC1"          1 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT DELETE ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT INSERT ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT SELECT ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT UPDATE ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
Import terminated successfully with warnings.

此处的授权信息执行失败的提示信息已经说明了问题。

3）创建sec2用户，完成对sec2用户的导入
 ora10g @secDB2 /imp$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Feb 26 21:47:43 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

sys@ ora10g > create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ ora10g > grant connect,resource,dba to sec2;

Grant succeeded.

sys@ ora10g > exit

 ora10g @secDB2 /imp$ imp sec2/sec2 file=sec2.dmp log=sec2.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:48:17 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC2's objects into SEC2
. . importing table                       "T_SEC2"          1 rows imported
IMP-00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "SEC2"."V_SEC2"                            ("SEC1_X","SEC"
 "2_X") AS "
 "select SYN_T_SEC1.x sec1_x, T_SEC2.x sec2_x from SYN_T_SEC1,T_SEC2"
Import terminated successfully with warnings.

问题又一次再现，错误原因与我们前面分析的结果相同。

14.小结
我们使用EXP/IMP工具在不同用户间存在较复杂的授权关系的情况下完成数据迁移时，需要特别注意他们的先后顺序。
为避免此类错误的发生，建议在多用户数据迁移场景下，使用sys用户一次性完成（使用OWNER参数）数据迁移工作。

问题现象是这样的，在IMP数据的过程中出现“ IMP-00041: Warning: object created with compilation warnings ”错误，因这个错误导致视图导入后无法使用。经分析，发现此问题与“授权信息丢失”有关。
为避免朋友们走弯路，我模拟再现一下这个问题，同时给出一个分析和解决问题的思路。

1.创建两个用户sec1和sec2
sys@ora10g> create user sec1 identified by sec1 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource to sec1;

Grant succeeded.

sys@ora10g> create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource to sec2;

Grant succeeded.

2.在第一个用户sec1中创建表T_SEC1
sys@ora10g> conn sec1/sec1
Connected.
sec1@ora10g> create table t_sec1 (x int);

Table created.

sec1@ora10g> insert into t_sec1 values (1);

1 row created.

3.在第二个用户sec2中创建表T_SEC2
sec1@ora10g> conn sec2/sec2
Connected.
sec2@ora10g> create table t_sec2 (x int);

Table created.

sec2@ora10g> insert into t_sec2 values (2);

1 row created.

4.在sec2中创建sec1用户中t_sec1表的同名
sec2@ora10g> conn / as sysdba
Connected.
sys@ora10g> create synonym sec2.syn_t_sec1 for sec1.t_sec1;

Synonym created.

sys@ora10g> grant insert,delete,update,select on sec1.t_sec1 to sec2;

Grant succeeded.

5.在sec2用户下创建视图v_sec2
该视图同时使用到刚刚在sec2用户下创建的同名SYN_T_SEC1和表T_SEC2。
sec2@ora10g> conn / as sysdba
Connected.
sys@ora10g>
sys@ora10g> grant create view to sec2;

Grant succeeded.

sys@ora10g> conn sec2/sec2
Connected.
sec2@ora10g> create view v_sec2 as select SYN_T_SEC1.x sec1_x, T_SEC2.x sec2_x from SYN_T_SEC1,T_SEC2;

View created.

6.分别看一下各个用户下的数据库对象
sec2@ora10g> conn sec1/sec1
Connected.
sec1@ora10g> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
T_SEC1                         TABLE


sec2@ora10g> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
T_SEC2                         TABLE
SYN_T_SEC1                     SYNONYM
V_SEC2                         VIEW

7.我们这里分别生成sec1和sec2用户的备份
ora10g@secDB1 /exp$ exp sec1/sec1 file=sec1.dmp log=sec1.log

Export: Release 10.2.0.3.0 - Production on Fri Feb 26 03:01:40 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC1
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC1
About to export SEC1's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC1's tables via Conventional Path ...
. . exporting table                         T_SEC1          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

ora10g@secDB1 /exp$ exp sec2/sec2 file=sec2.dmp log=sec2.log

Export: Release 10.2.0.3.0 - Production on Fri Feb 26 03:01:56 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC2
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC2
About to export SEC2's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC2's tables via Conventional Path ...
. . exporting table                         T_SEC2          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

8.将备份文件发送到待导入的服务器secDB2
ora10g@secDB1 /exp$ scp sec1.dmp sec2.dmp 172.17.193.201:/imp
oracle@172.17.193.201's password:
sec1.dmp  100%   16KB  16.0KB/s   00:00
sec2.dmp  100%   16KB  16.0KB/s   00:00

9.在secDB2服务器上创建同样的用户，并进行导入测试
1）创建用户并授权
sys@ora10g> create user sec1 identified by sec1 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource to sec1;

Grant succeeded.

sys@ora10g> create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource,dba to sec2;

Grant succeeded.

2）导入测试
（1）先导入sec1用户
ora10g@secDB2 /imp$ imp sec1/sec1 file=sec1.dmp log=sec1.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 11:58:09 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC1's objects into SEC1
. . importing table                       "T_SEC1"          1 rows imported
Import terminated successfully without warnings.

（2）导入sec2用户
 ora10g @secDB2 /imp$ imp sec2/sec2 file=sec2.dmp log=sec2.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:14:59 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC2's objects into SEC2
. . importing table                       "T_SEC2"          1 rows imported
Import terminated successfully without warnings.

此时是导入成功的！演示还未结束，请继续。

（3）假如此时删除sec2用户（删除的目的可能有很多，比如之前授予的权限不足等），对其进行重新创建后再完成数据导入
sys@ ora10g > drop user sec2 cascade;

User dropped.

sys@ ora10g > create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ ora10g > grant connect,resource,dba to sec2;

Grant succeeded.

（4）再重新完成sec2用户的IMP导入
 ora10g @secDB2 /imp$ imp sec2/sec2 file=sec2.dmp log=sec2.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:20:48 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC2's objects into SEC2
. . importing table                       "T_SEC2"          1 rows imported
IMP-00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "SEC2"."V_SEC2"                            ("SEC1_X","SEC"
 "2_X") AS "
 "select SYN_T_SEC1.x sec1_x, T_SEC2.x sec2_x from SYN_T_SEC1,T_SEC2"
Import terminated successfully with warnings.

此时，问题出现了，此时的“IMP-00041”错误提示内容是创建的视图存在编译错误（这个错误没有太大的指导意义）。

10.验证被导入的内容是否可用
1）导入了三个对象，没有问题。
sec2@ora10g> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
SYN_T_SEC1                     SYNONYM
T_SEC2                         TABLE
V_SEC2                         VIEW

2）T_SEC2表可用
sec2@ora10g> select * from T_SEC2;

         X
----------
         2

3）SYN_T_SEC1同名可用
sec2@ora10g> select * from SYN_T_SEC1;

         X
----------
         1

4）此时视图不可用，提示存在错误。原因不详细。
sec2@ora10g> select * from V_SEC2;
select * from V_SEC2
              *
ERROR at line 1:
ORA-04063: view "SEC2.V_SEC2" has errors

5）尝试重新编译，无效。
sec2@ora10g> alter view V_SEC2 compile;

Warning: View altered with compilation errors.

6）没有具体的错误提示信息
sec2@ora10g> show errors;
No errors.

11.问题原因
根本原因在于，当删除sec2用户重新创建后，sec2用户原来具有的sec1用户下T_SEC1表授权信息丢失了。
不要着急，我们来分析一下。
在完成sec1用户导入后，其实sec1用户的dmp文件中包含的授权信息已经完成对sec2用户的授权。我们使用“show=y”选项查看一下sec1用户的dmp文件内容。
 ora10g @secDB2 /imp$ imp sec1/sec1 file=sec1.dmp log=sec1.log ignore=y full=y show=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:24:50 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC1's objects into SEC1
 "BEGIN  "
 "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
 "CURRENT_SCHEMA'), export_db_name=>'ORA10G', inst_scn=>'36699433');"
 "COMMIT; END;"
 "CREATE TABLE "T_SEC1" ("X" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 M"
 "AXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL"
 " DEFAULT) TABLESPACE "TBS_SEC_D" LOGGING NOCOMPRESS"
. . skipping table "T_SEC1"

 "GRANT DELETE ON "T_SEC1" TO "SEC2""
 "GRANT INSERT ON "T_SEC1" TO "SEC2""
 "GRANT SELECT ON "T_SEC1" TO "SEC2""
 "GRANT UPDATE ON "T_SEC1" TO "SEC2""
Import terminated successfully without warnings.

注意最后四行的授权信息。这些授权信息是在sec1用户数据导入过程中同时完成的。

12.问题处理
既然知道了问题的出处，处理就简单了。以sys用户显示的将sec1用户下t_sec1表的操作权限授予sec2用户，然后再重新对问题视图进行编译（当然，重新创建这个视图亦可）。
sec2@ora10g> conn / as sysdba
Connected.
sys@ora10g> grant insert,delete,update,select on sec1.t_sec1 to sec2;

Grant succeeded.

sys@ora10g> conn sec2/sec2
Connected.
sec2@ora10g> alter view V_SEC2 compile;

View altered.

sec2@ora10g> select * from V_SEC2;

    SEC1_X     SEC2_X
---------- ----------
         1          2

1 row selected.

OK，问题到此处理完毕。

13.另外一种导致这个问题的场景演示
还用一种可能出现这种问题的可能性，如果是按照下面的顺序在secDB2服务器上完成用户的创建和导入，一样会报上面的错误。
创建sec1用户，完成对sec1用户的导入；
创建sec2用户，完成对sec2用户的导入。

因为在完成sec1用户导入后，授权信息的授予对象sec2还不存在！

为保证信息的完整性和正确性，赘述在此。
1）删除sec1和sec2用户
sys@ ora10g > drop user sec1 cascade;

User dropped.

sys@ ora10g > drop user sec2 cascade;

User dropped.

2）创建sec1用户，完成对sec1用户的导入
sys@ ora10g > create user sec1 identified by sec1 default tablespace TBS_SEC_D;

User created.

sys@ ora10g > grant connect,resource to sec1;

Grant succeeded.

sys@ ora10g > exit

 ora10g @secDB2 /imp$ imp sec1/sec1 file=sec1.dmp log=sec1.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:47:11 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC1's objects into SEC1
. . importing table                       "T_SEC1"          1 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT DELETE ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT INSERT ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT SELECT ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT UPDATE ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
Import terminated successfully with warnings.

此处的授权信息执行失败的提示信息已经说明了问题。

3）创建sec2用户，完成对sec2用户的导入
 ora10g @secDB2 /imp$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Feb 26 21:47:43 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

sys@ ora10g > create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ ora10g > grant connect,resource,dba to sec2;

Grant succeeded.

sys@ ora10g > exit

 ora10g @secDB2 /imp$ imp sec2/sec2 file=sec2.dmp log=sec2.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:48:17 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC2's objects into SEC2
. . importing table                       "T_SEC2"          1 rows imported
IMP-00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "SEC2"."V_SEC2"                            ("SEC1_X","SEC"
 "2_X") AS "
 "select SYN_T_SEC1.x sec1_x, T_SEC2.x sec2_x from SYN_T_SEC1,T_SEC2"
Import terminated successfully with warnings.

问题又一次再现，错误原因与我们前面分析的结果相同。

14.小结
我们使用EXP/IMP工具在不同用户间存在较复杂的授权关系的情况下完成数据迁移时，需要特别注意他们的先后顺序。
为避免此类错误的发生，建议在多用户数据迁移场景下，使用sys用户一次性完成（使用OWNER参数）数据迁移工作。


文章标签：
GPU云服务器
关系型数据库
Oracle
数据库管理
OLAP
安全
数据库
相关实践学习
部署Stable Diffusion玩转AI绘画（GPU云服务器）
本实验通过在ECS上从零开始部署Stable Diffusion来进行AI绘画创作，开启AIGC盲盒。
白及88
目录
相关文章
爱你三千遍斯塔克
|
4月前
|
JavaScript
Error loading saved preferences: ~/.vuerc may be corrupted or have syntax errors. Please fix/delete
Error loading saved preferences: ~/.vuerc may be corrupted or have syntax errors. Please fix/delete
爱你三千遍斯塔克
29 0
123hello123
|
12月前
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘thinkphp.test‘ don‘t exsit
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘thinkphp.test‘ don‘t exsit
123hello123
300 0
No8g攻城狮
|
SQL Java 数据库连接
sql injection violation, syntax error: syntax error, error in :‘**‘expect IDENTIFIER, actual IDENTIF
sql injection violation, syntax error: syntax error, error in :‘**‘expect IDENTIFIER, actual IDENTIF
No8g攻城狮
200 0
heardic
|
关系型数据库 MySQL Linux
SQLSTATE[HY000]: General error: 1364 Field ‘xxx’ doesn't have a default value 解决办法
SQLSTATE[HY000]: General error: 1364 Field ‘xxx’ doesn't have a default value 解决办法
heardic
1500 0
姚远OracleACE
|
数据库
ORA-06553: PLS-801: internal error 的解决办法
搜索了一下，原来是把32位的数据库恢复到64位的数据库了。找到解决方案如下
姚远OracleACE
163 0
rudy_gao
|
SQL 关系型数据库 Oracle
ORA-01466: unable to read data - table definition has changed
1. Oracle建议我们等待大约5分钟之后再进行flashback query新创建的表，否则可能会碰到这个错误ORA-01466: unable to read data - table definition has changed.
rudy_gao
1788 0
李博 bluemind
|
安全
Error ORA-20001: SQL_PLSQL_ERROR: N, ROUTINE, MO_GLOBAL.INIT
李博 bluemind
3280 0
李博 bluemind
|
Oracle 关系型数据库 Perl
ORA-06550 ，has been detected in fnd_global.initialize[fnd_init_sql].
李博 bluemind
1216 0
科技探索者
|
Linux PHP
error: expected declaration specifiers or ‘...’ before ‘FILE’
科技探索者
1904 0
科技小能手
|
Oracle 关系型数据库 SQL
Setting an Oracle event:The structure of the trace syntax
科技小能手
1008 0