oracle exp jobs,exp/imp时应注意JOB是否正确

exp/imp时的确应该注意JOB是否正确。不过还好，我们的数据库一般的用户都没有JOB，只有一个上面有，还是记录一下。

[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 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 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 ;

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 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

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 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

Connected.

SQL> drop user b cascade;

User dropped.

SQL> conn

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 file=full.dmp full=y

SQL> GRANT CONNECT, RESOURCE TO B IDENTIFIED BY B;

SQL> conn

Connected.

SQL> exec dbms_job.remove(63);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

[oracle@rhel131 ~]$ imp 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信息是否已经正确的导入。