exp/imp时的确应该注意JOB是否正确。不过还好，我们的数据库一般的用户都没有JOB，只有一个上面有，还是记录一下。
http://yangtingkun.itpub.net/post/468/40426
[@more@][oracle@rhel131 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 8 09:18:29 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> grant connect,resource to a identified by a;
Grant succeeded.
SQL> create table a.t as select rownum id from tab;
Table created.
SQL> conn a/a;
Connected.
SQL> declare
2 v_job number;
3 begin
4 dbms_job.submit(V_job,'NULL;',sysdate,'sysdate+1/24');
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> column job format 999
SQL> column priv_user format a10;
SQL> column schema_user format a10
SQL> column what format a20;
SQL> select job,priv_user,schema_user,what from all_jobs;
JOB PRIV_USER SCHEMA_USE WHAT
---- ---------- ---------- --------------------
21 A A NULL;
SQL> conn eric/eric;
Connected.
SQL> grant connect,resource to b identified by b;
Grant succeeded.
先用a用户导出自己的内容。
[oracle@rhel131 ~]$ exp a/a@orcl file=a.dmp
Export: Release 10.2.0.1.0 - Production on Thu Jan 8 09:25:48 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user A
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user A
About to export A's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export A's tables via Conventional Path ...
. . exporting table T 3643 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.
导入到B用户
[oracle@rhel131 ~]$ imp eric/eric@orcl file=a.dmp fromuser=a touser=b;
Import: Release 10.2.0.1.0 - Production on Thu Jan 8 09:27:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by A, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing A's objects into B
. . importing table "T" 3643 rows imported
IMP-00017: following statement failed with ORACLE error 1:
"BEGIN DBMS_JOB.ISUBMIT(JOB=>21,WHAT=>'NULL;',NEXT_DATE=>TO_DATE('2009-01-"
"08:10:19:49','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>'sysdate+1/24',NO_PARSE=>TR"
"UE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 97
ORA-06512: at line 1
Import terminated successfully with warnings.
由于job存在，这个job导入失败。
删掉A的这个job后再试试
SQL> conn a/a@orcl;
Connected.
SQL> exec dbms_job.remove(21);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select job,priv_user,schema_user,what from all_jobs;
no rows selected
SQL> conn eric/eric
Connected.
SQL> drop user b cascade;
User dropped.
SQL> GRANT CONNECT, RESOURCE TO B IDENTIFIED BY B;
Grant succeeded.
[oracle@rhel131 ~]$ imp eric/eric@orcl file=a.dmp fromuser=a touser=b;
Import: Release 10.2.0.1.0 - Production on Thu Jan 8 09:32:08 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by A, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing A's objects into B
. . importing table "T" 3643 rows imported
Import terminated successfully without warnings.
成功的导入，发现JOB虽然导入B用户下，但是却在ERIC用户下运行。
SQL> conn eric/eric@orcl
Connected.
SQL> column job format 999
SQL> column priv_user format a10;
SQL> column schema_user format a10
SQL> column what format a20;
SQL> select job,priv_user,schema_user,what from all_jobs;
JOB PRIV_USER SCHEMA_USE WHAT
---- ---------- ---------- --------------------
21 ERIC B NULL;
应该没有办法直接改JOB所属的用户，只能删掉重新建立。
要想正确导入JOB只能采用下面的方法：
SQL> drop user b cascade;
User dropped.
SQL> GRANT CONNECT, RESOURCE TO B IDENTIFIED BY B;
Grant succeeded.
[oracle@rhel131 ~]$ imp b/b@orcl file=a.dmp full=y
Import: Release 10.2.0.1.0 - Production on Thu Jan 8 09:37:14 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by A, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing A's objects into B
. . importing table "T" 3643 rows imported
Import terminated successfully without warnings.
SQL> conn b/b
Connected.
SQL> select job,priv_user,schema_user,what from all_jobs;
JOB PRIV_USER SCHEMA_USE WHAT
---- ---------- ---------- --------------------
21 B B NULL;
但是有个前提就是A用户必须已删除这个JOB了，否则还是会出现ORA-00001: unique constraint
(SYS.I_JOB_JOB) violated的错误。
最后再看看全库导入/导出的情况。
先恢复一下用户A和B
SQL> conn eric/eric@orcl
Connected.
SQL> drop user b cascade;
User dropped.
SQL> conn a/a@orcl
Connected.
SQL> declare
2 v_job number;
3 begin
4 dbms_job.submit(V_job,'NULL;',sysdate,'sysdate+1/24');
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> column job format 999
SQL> column priv_user format a10;
SQL> column schema_user format a10
SQL> column what format a20;
SQL> select job,priv_user,schema_user,what from all_jobs;
JOB PRIV_USER SCHEMA_USE WHAT
---- ---------- ---------- --------------------
63 A A NULL;
[oracle@rhel131 ~]$ exp eric/eric@orcl file=full.dmp full=y
SQL> GRANT CONNECT, RESOURCE TO B IDENTIFIED BY B;
SQL> conn a/a@orcl
Connected.
SQL> exec dbms_job.remove(63);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
[oracle@rhel131 ~]$ imp eric/eric@orcl file=full.dmp fromuser=a touser=b;
Import: Release 10.2.0.1.0 - Production on Thu Jan 8 09:54:12 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing A's objects into B
. . importing table "T" 3643 rows imported
Import terminated successfully without warnings.
SQL> conn eric/eric @orcl;
Connected.
SQL> select job,priv_user,schema_user,what from all_jobs;
no rows selected
从全库逻辑备份按用户导入时，Oracle没有导入JOB，不知道这个是Oracle的bug，还是故意这样实现的。
这个问题在导入的用户名和导出的用户名不同时出现，如果导入的用户名和导出的用户名一致，则不会出
现这个问题。怀疑是Oracle调用DBMS_IJOB包时出现的问题。
总之，EXP/IMP的时候需要考虑JOB，并在操作结束后检查JOB信息是否已经正确的导入。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271283/viewspace-1015878/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/271283/viewspace-1015878/
扫一扫
372
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
