远程数据库作业
select* from dba_scheduler_external_dests;DESTINATION_NAMEHOSTNAMEPORTIP_ADDRESSENABLEDCOMMENTS
1STATION199station1991500192.168.0.199TRUERegistered on 04-AUG-18 05.55.04.276715 PM PRC
begin
dbms_scheduler.create_database_destination(
destination_name => 'db199',
agent => 'station199',tns_name => 'orcl199');
end;
select* from dba_scheduler_db_dests;
OWNERDESTINATION_NAMECONNECT_INFOAGENTENABLEDREFS_ENABLEDCOMMENTS
1SYSDB199orcl199STATION199TRUETRUE
begin
dbms_scheduler.create_credential(credential_name => 'CREDENTIAL1',
username =>'HR',
password => 'hr');
end;
grant execute on credential1 to hr;
grant all on db199 to hr;
BEGIN
dbms_scheduler.create_job(
job_name => 'hr.job13',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update employees set salary=salary+1where employee_id=100;
commit;
end;',
start_date => systimestamp at time zone 'Asia/Shanghai',
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB13',
auto_drop => FALSE,
credential_name =>'"SYS"."CREDENTIAL1"' ,destination_name =>'db199' ,
enabled => true);
END;
如果是一个存储过程,目的地那边要有这个存储过程吗:
在station199上建存储过程:
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'hr.job131',
job_type => 'STORED_PROCEDURE',
job_action => 'hr.procremotedb_win',
start_date => systimestamp at time zone 'PRC',
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job131',
auto_drop => FALSE,
enabled => FALSE,
credential_name => 'sys.credential1',
destination_name => 'db199');
end;
begin
sys.dbms_scheduler.enable( '"HR"."JOB131"' );
END;
页:
[1]