社区 › SECOUG第一期长期技术沙龙 › 第33次活动：2017-11-15（星期三晚上7:00-9:30）

botang 发表于 2017-11-15 20:14:37

第33次活动：2017-11-15（星期三晚上7:00-9:30）

select* from dba_scheduler_external_dests;

DECLARE
versionnum VARCHAR2(30);
BEGIN
versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
DBMS_OUTPUT.PUT_LINE(versionnum);
END;

alter user hr profile default;

select* from dba_tab_privs tp where
tp.grantee='HR';

grant all on station17 to hr;

select* from dict where table_name like 'DBA%DESTS';

select* from dba_scheduler_db_dests;

----

begin
   dbms_scheduler.create_database_destination(destination_name => 'db199utf',agent => 'station199',
   tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = utforcl.example.com)))');
   end;
   
   ---
   begin
   dbms_scheduler.create_database_destination(destination_name => 'db199orcl',agent => 'station199',
   tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.example.com)))');
end;

----
begin
dbms_scheduler.create_credential(
      credential_name => 'cred2',
      username => 'hr',
      password => 'hr');
end;
-----

select* from dba_scheduler_db_dests;

grant execute on cred2 to hr;

grant all on db199utf to hr;
grant all on db199orcl to hr;

/* EM ERROR:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB13"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update salary=salary+1 where employee_id=100;
commit;
end;',
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job13',
auto_drop => FALSE,
enabled => FALSE);
DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB13"', attribute => 'destination', value => 'SYS.DB199UTF' );
DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB13"', attribute => 'credential_name', value => '"SYS"."CRED2"' );
sys.dbms_scheduler.enable( '"HR"."JOB13"' );
END;
*/
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB131"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update employees set salary=salary+1 where employee_id=100;
commit;
end;',
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job131',
auto_drop => FALSE,
enabled => true,
destination_name => 'SYS.DB199UTF' ,
credential_name=> '"SYS"."CRED2"'
);
END;


create or replace procedure hr.proc132 is
begin
    update employees set salary=salary+1 where employee_id=100;
    commit;
end;


BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB132"',
job_type => 'stored_procedure',
job_action =>'hr.proc132',
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job132',
auto_drop => FALSE,
enabled => true,
destination_name => 'SYS.DB199ORCL' ,
credential_name=> '"SYS"."CRED2"'
);
END;


BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB133"',
job_type => 'stored_procedure',
job_action =>'hr.proc132',
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job133',
auto_drop => FALSE,
enabled => true,
destination_name => 'SYS.DB199ORCL' ,
credential_name=> '"SYS"."CRED2"'
);
END;



begin
   dbms_scheduler.create_database_destination(destination_name => 'db17orcl',agent => 'station17',
   tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))');
   end;

grant all on db17orcl to hr;

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB134"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update employees set salary=salary+1 where employee_id=100;
commit;
end;',
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job134',
auto_drop => FALSE,
enabled => true,
destination_name => 'SYS.DB17ORCL' ,
credential_name=> '"SYS"."CRED2"'
);
END;界面点不了：


group和 group member：



select * from dba_rsrc_plansp
where p.plan='DEFAULT_PLAN';

select * from dba_rsrc_plan_directivesp
where p.plan='DEFAULT_PLAN';

select * from dba_rsrc_plansp
where p.plan='ORA$AUTOTASK_SUB_PLAN';

select * from dba_rsrc_plan_directivesp
where p.plan='ORA$AUTOTASK_SUB_PLAN';

查看完整版本: 第33次活动：2017-11-15（星期三晚上7:00-9:30）