Bo's Oracle Station

查看: 2020|回复: 0

远程数据库作业

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-8-4 17:57:43 | 显示全部楼层 |阅读模式
  1.     select  * from dba_scheduler_external_dests;
复制代码
   DESTINATION_NAMEHOSTNAMEPORTIP_ADDRESSENABLEDCOMMENTS
1STATION199station1991500192.168.0.199TRUERegistered on 04-AUG-18 05.55.04.276715 PM PRC

  1. begin
  2.    dbms_scheduler.create_database_destination(
  3.    destination_name => 'db199',
  4.    agent => 'station199',tns_name => 'orcl199');
  5. end;
复制代码
  1. select  * from dba_scheduler_db_dests;
复制代码
   OWNERDESTINATION_NAMECONNECT_INFOAGENTENABLEDREFS_ENABLEDCOMMENTS
1SYSDB199orcl199STATION199TRUETRUE

  1. begin
  2.    dbms_scheduler.create_credential(credential_name => 'CREDENTIAL1',
  3.                                                              username =>'HR',
  4.                                                              password => 'hr');
  5. end;

  6. grant execute on credential1 to hr;
复制代码
  1. grant all on db199 to hr;
复制代码
  1. BEGIN
  2. dbms_scheduler.create_job(
  3. job_name => 'hr.job13',
  4. job_type => 'PLSQL_BLOCK',
  5. job_action => 'begin
  6.    update employees set salary=salary+1  where employee_id=100;
  7.    commit;
  8. end;',
  9. start_date => systimestamp at time zone 'Asia/Shanghai',
  10. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
  11. job_class => '"DEFAULT_JOB_CLASS"',
  12. comments => 'JOB13',
  13. auto_drop => FALSE,
  14. credential_name =>'"SYS"."CREDENTIAL1"' ,destination_name =>'db199' ,
  15. enabled => true);
  16. END;
复制代码

如果是一个存储过程,目的地那边要有这个存储过程吗:

在station199上建存储过程:


  1. BEGIN
  2. sys.dbms_scheduler.create_job(
  3. job_name => 'hr.job131',
  4. job_type => 'STORED_PROCEDURE',
  5. job_action => 'hr.procremotedb_win',
  6. start_date => systimestamp at time zone 'PRC',
  7. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
  8. job_class => '"DEFAULT_JOB_CLASS"',
  9. comments => 'job131',
  10. auto_drop => FALSE,
  11. enabled => FALSE,
  12. credential_name => 'sys.credential1',
  13. destination_name => 'db199');
  14. end;
复制代码
  1. begin
  2. sys.dbms_scheduler.enable( '"HR"."JOB131"' );
  3. END;
复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 16:15 , Processed in 0.027185 second(s), 24 queries .

快速回复 返回顶部 返回列表