远程数据库作业

botang

1.     select  * from dba_scheduler_external_dests;

复制代码

	DESTINATION_NAME	HOSTNAME	PORT	IP_ADDRESS	ENABLED	COMMENTS
1	STATION199	station199	1500	192.168.0.199	TRUE	Registered 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;

复制代码

	OWNER	DESTINATION_NAME	CONNECT_INFO	AGENT	ENABLED	REFS_ENABLED	COMMENTS
1	SYS	DB199	orcl199	STATION199	TRUE	TRUE

1. begin
   
2.    dbms_scheduler.create_credential(credential_name => 'CREDENTIAL1',
   
3.                                                              username =>'HR',
   
4.                                                              password => 'hr');
   
5. end;
   
6. 
   
7. 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;

复制代码