oracle 数据库中，如何迁移 job?
1. 查看当前数据库中的job
sys@nocdb> select job, log_user,priv_user ,schema_user,next_date from dba_jobs order by 1;
job log_user priv_user schema_user next_date
---------- --------------- --------------- -------------------- ---------------------------------------------------------------------------
2 sxc sxc sxc 01-jun-23 02.22.02.000000 pm 08:00
2. expdp导出job
[oracle@ora19c dmp]$ expdp \'/ as sysdba\' directory=dir_exp dumpfile=jobs.dmp full=y include=job logfile=job_expdp.log
export: release 19.0.0.0.0 - production on thu jun 1 13:30:34 2023
version 19.3.0.0.0
米乐app官网下载 copyright (c) 1982, 2019, oracle and/or its affiliates. all rights reserved.
password:
connected to: oracle database 19c enterprise edition release 19.0.0.0.0 - production
starting "sys"."sys_export_full_01": "/******** as sysdba" directory=dir_exp dumpfile=jobs.dmp full=y include=job logfile=job_expdp.log
processing object type database_export/schema/job
master table "sys"."sys_export_full_01" successfully loaded/unloaded
******************************************************************************
dump file set for sys.sys_export_full_01 is:
/home/oracle/dmp/jobs.dmp
job "sys"."sys_export_full_01" successfully completed at thu jun 1 13:30:42 2023 elapsed 0 00:00:05
3. impdp导入job
--1)这里，我们就在原环境导入，先把原来的job删掉
sys@nocdb> begin
2 sys.dbms_job.remove(2);
3 commit;
4 end;
5 /
pl/sql procedure successfully completed.
sys@nocdb> select job, log_user,priv_user ,schema_user,next_date from dba_jobs order by 1;
no rows selected
--2)impdp导入job
[oracle@ora19c dmp]$ impdp \'/ as sysdba\' directory=dir_exp dumpfile=jobs.dmp logfile=job_impdp.log
import: release 19.0.0.0.0 - production on thu jun 1 13:33:18 2023
version 19.3.0.0.0
米乐app官网下载 copyright (c) 1982, 2019, oracle and/or its affiliates. all rights reserved.
password:
connected to: oracle database 19c enterprise edition release 19.0.0.0.0 - production
master table "sys"."sys_import_full_01" successfully loaded/unloaded
starting "sys"."sys_import_full_01": "/******** as sysdba" directory=dir_exp dumpfile=jobs.dmp logfile=job_impdp.log
processing object type database_export/schema/job
job "sys"."sys_import_full_01" successfully completed at thu jun 1 13:33:24 2023 elapsed 0 00:00:03
4. 检查job
sys@nocdb> select job, log_user,priv_user ,schema_user,next_date from dba_jobs order by 1;
job log_user priv_user schema_user next_date
---------- --------------- --------------- -------------------- ---------------------------------------------------------------------------
2 sys sxc sxc 01-jun-23 02.22.02.000000 pm 00:00
结论：job 已成功导入到数据库中，但是我们可以看到log_user是我们导入的用户sys，并不是之前的sxc用户。
但是，我们知道log_user即使是sys用户，其实并不影响job正常执行。如果客户要求log_user必须和原环境保持一致，那么，我们需要怎么修改呢？
在11g 环境中，我们可以直接update dba_jobs视图，在19c环境中，不行，会抛出如下错误：参考文档：https://www.modb.pro/db/430507
sys@nocdb> update dba_jobs set log_user='sxc' where schema_user='sxc';
update dba_jobs set log_user='sxc' where schema_user='sxc'
*
error at line 1:
ora-01779: cannot modify a column which maps to a non key-preserved table
5. 修改log_user
方法1： 使用toad等工具
– 1) 在原环境上，通过toad 连接数据库，捞取job创建语句
declare
x number;
user_name varchar2(30);
begin
select user into user_name from dual;
execute immediate 'alter session set current_schema = sxc';
begin
sys.dbms_job.submit
( job => x
,what => 'part_tab_add_delete;'
,next_date => to_date('01/06/2023 14:22:02','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate 1/24'
,no_parse => false
);
sys.dbms_output.put_line('job number is: ' || to_char(x));
execute immediate 'alter session set current_schema = ' || user_name ;
exception
when others then
execute immediate 'alter session set current_schema = ' || user_name ;
raise;
end;
commit;
end;
/
–2) 连接到sxc用户下执行
sxc@nocdb> show user
user is "sxc"
sxc@nocdb> declare
x number;
user_name varchar2(30);
begin
select user into user_name from dual;
execute immediate 'alter session set current_schema = sxc';
begin
sys.dbms_job.submit
( job => x
,what => 'part_tab_add_delete;'
,next_date => to_date('01/06/2023 14:22:02','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate 1/24'
,no_parse => false
);
sys.dbms_output.put_line('job number is: ' || to_char(x));
execute immediate 'alter session set current_schema = ' || user_name ;
exception
when others then
execute immediate 'alter session set current_schema = ' || user_name ;
raise;
end;
commit;
end;
24 /
pl/sql procedure successfully completed.
sys@nocdb> select job, log_user,priv_user ,schema_user,next_date from dba_jobs order by 1;
job log_user priv_user schema_user next_date
---------- --------------- --------------- -------------------- ---------------------------------------------------------------------------
45 sxc sxc sxc 01-jun-23 02.22.02.000000 pm 08:00
结论：job 创建成功，log_user 和 原环境保持一致，但是job 号从2变成了45。注意：需要有create job权限，否则创建job会抛出如下错误：
error at line 1: ora-27486: insufficient privileges ora-06512: at line 20 ora-06512: at "sys.dbms_isched", line 9387 ora-06512: at "sys.dbms_isched", line 9376 ora-06512: at "sys.dbms_isched", line 175 ora-06512: at "sys.dbms_isched", line 9302 ora-06512: at "sys.dbms_ijob", line 196 ora-06512: at "sys.dbms_job", line 168 ora-06512: at line 8
方法2： 利用dmp文件生成sql语句
–1) 如果没有toad等工具，我们可以利用impdp中sqlfile参数，生成创建job的语句
[oracle@ora19c dmp]$ impdp \'/ as sysdba\' directory=dir_exp dumpfile=jobs.dmp logfile=job_impdp.log sqlfile=job.sql
import: release 19.0.0.0.0 - production on thu jun 1 13:51:19 2023
version 19.3.0.0.0
米乐app官网下载 copyright (c) 1982, 2019, oracle and/or its affiliates. all rights reserved.
password:
connected to: oracle database 19c enterprise edition release 19.0.0.0.0 - production
master table "sys"."sys_sql_file_full_01" successfully loaded/unloaded
starting "sys"."sys_sql_file_full_01": "/******** as sysdba" directory=dir_exp dumpfile=jobs.dmp logfile=job_impdp.log sqlfile=job.sql
processing object type database_export/schema/job
job "sys"."sys_sql_file_full_01" successfully completed at thu jun 1 13:51:24 2023 elapsed 0 00:00:02
-- 查看创建job语句
[oracle@ora19c dmp]$ cat job.sql
-- connect sys
alter session set events '10150 trace name context forever, level 1';
alter session set events '10904 trace name context forever, level 1';
alter session set events '25475 trace name context forever, level 1';
alter session set events '10407 trace name context forever, level 1';
alter session set events '10851 trace name context forever, level 1';
alter session set events '22830 trace name context forever, level 192 ';
-- new object type path: database_export/schema/job
begin sys.dbms_ijob.submit(
job=> 2,
luser=> 'sxc',
puser=> 'sxc',
cuser=> 'sxc',
next_date=> to_date('2023-06-01 14:22:02', 'yyyy-mm-dd:hh24:mi:ss'),
interval=> 'sysdate 1/24',
broken=> false,
what=> 'part_tab_add_delete;',
nlsenv=> 'nls_language=''american'' nls_territory=''america'' nls_currency=''$'' nls_iso_currency=''america'' nls_numeric_characters=''.,'' nls_calendar=''gregorian'' nls_date_format=''yyyy-mm-dd hh24:mi:ss'' nls_date_language=''american'' nls_sort=''binary'' nls_time_format=''hh.mi.ssxff am'' nls_timestamp_format=''dd-mon-rr hh.mi.ssxff am'' nls_time_tz_format=''hh.mi.ssxff am tzr'' nls_timestamp_tz_format=''dd-mon-rr hh.mi.ssxff am tzr'' nls_dual_currency=''$'' nls_comp=''binary'' nls_length_semantics=''byte'' nls_nchar_conv_excp=''false''',
env=> '0102000000000000');
end;
/
-- fixup virtual columns...
-- done fixup virtual columns
–2) 连接到sxc用户下执行
sxc@nocdb> show user
user is "sxc"
sxc@nocdb> begin sys.dbms_ijob.submit(
job=> 2,
luser=> 'sxc',
puser=> 'sxc',
cuser=> 'sxc',
next_date=> to_date('2023-06-01 14:22:02', 'yyyy-mm-dd:hh24:mi:ss'),
interval=> 'sysdate 1/24',
broken=> false,
what=> 'part_tab_add_delete;',
nlsenv=> 'nls_language=''american'' nls_territory=''america'' nls_currency=''$'' nls_iso_currency=''america'' nls_numeric_characters=''.,'' nls_calendar=''gregorian'' nls_date_format=''yyyy-mm-dd hh24:mi:ss'' nls_date_language=''american'' nls_sort=''binary'' nls_time_format=''hh.mi.ssxff am'' nls_timestamp_format=''dd-mon-rr hh.mi.ssxff am'' nls_time_tz_format=''hh.mi.ssxff am tzr'' nls_timestamp_tz_format=''dd-mon-rr hh.mi.ssxff am tzr'' nls_dual_currency=''$'' nls_comp=''binary'' nls_length_semantics=''byte'' nls_nchar_conv_excp=''false''',
env=> '0102000000000000');
end;
13 /
begin sys.dbms_ijob.submit(
*
error at line 1:
ora-06550: line 1, column 8:
pls-00201: identifier 'sys.dbms_ijob' must be declared
ora-06550: line 1, column 8:
pl/sql: statement ignored
结论：执行报错，经过对比可以看到，toad 工具生成的sql语句是调用的sys.dbms_job包，而impdp生成的是调用的sys.dbms_ijob包，两者不一样。 除了create job权限，还需要我们重新赋予其他权限，在执行。
--1) 赋予dbms_ijob包权限
sys@nocdb> grant execute on sys.dbms_ijob to sxc ;
grant succeeded.
sxc@nocdb> show user
user is "sxc"
sxc@nocdb> begin sys.dbms_ijob.submit(
job=> 2,
luser=> 'sxc',
puser=> 'sxc',
cuser=> 'sxc',
next_date=> to_date('2023-06-01 14:22:02', 'yyyy-mm-dd:hh24:mi:ss'),
interval=> 'sysdate 1/24',
broken=> false,
what=> 'part_tab_add_delete;',
nlsenv=> 'nls_language=''american'' nls_territory=''america'' nls_currency=''$'' nls_iso_currency=''america'' nls_numeric_characters=''.,'' nls_calendar=''gregorian'' nls_date_format=''yyyy-mm-dd hh24:mi:ss'' nls_date_language=''american'' nls_sort=''binary'' nls_time_format=''hh.mi.ssxff am'' nls_timestamp_format=''dd-mon-rr hh.mi.ssxff am'' nls_time_tz_format=''hh.mi.ssxff am tzr'' nls_timestamp_tz_format=''dd-mon-rr hh.mi.ssxff am tzr'' nls_dual_currency=''$'' nls_comp=''binary'' nls_length_semantics=''byte'' nls_nchar_conv_excp=''false''',
env=> '0102000000000000');
end;
13 /
pl/sql procedure successfully completed.
sxc@nocdb> commit; --> 注意，这里需要commit，生成的脚本中没有。
commit complete.
sys@nocdb> select job, log_user,priv_user ,schema_user,next_date from dba_jobs order by 1;
job log_user priv_user schema_user next_date
---------- --------------- --------------- -------------------- ---------------------------------------------------------------------------
2 sxc sxc sxc 01-jun-23 02.22.02.000000 pm 08:00
45 sxc sxc sxc 01-jun-23 02.22.02.000000 pm 08:00
结论：job 创建成功，job号、log_user 都和 原环境保持一致。但是需要多赋予sys.dbms_ijob包的执行权限。
最后修改时间：2023-06-02 09:02:21
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。