第78和79次：2014-12-28上下午

botang · 发表于 2014-12-28 12:10:02

本帖最后由 botang 于 2014-12-30 12:47 编辑

select * from dba_scheduler_credentials;
select * from dba_tab_privs tp where tp.grantee='HR';
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'filewatcher1',
directory_path => '/home/oracle/dir1',
file_name => 'myfile*',
credential_name => 'credential1');
end;
select * from dba_scheduler_file_watchers;
grant execute on filewatcher1 to hr;
grant execute on SYS.SCHEDULER_FILEWATCHER_RESULT to hr;
select * from dba_sys_privs sp where sp.grantee='HR';
create table hr.t05317_fw ( a varchar2(200) ) ;
create or replace procedure hr.procfilewatcher (p_1 SYS.SCHEDULER_FILEWATCHER_RESULT)
is
begin
insert into hr.t05317_fw values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||' '
||p_1.directory_path||' '
||p_1.actual_file_name||' '
||p_1.file_size ) ;
commit;
end;
begin
dbms_scheduler.create_program(
program_name => 'HR.progfilewatcher',
program_type => 'STORED_PROCEDURE',
program_action => 'HR.procfilewatcher',
number_of_arguments => 1,
enabled => false);
end;
select * from dba_scheduler_program_args a where a.owner='HR';
begin
dbms_scheduler.define_metadata_argument(
program_name => 'HR.progfilewatcher',
metadata_attribute => 'event_message',argument_position => 1 ) ;
end;
select * from dba_scheduler_program_args a where a.owner='HR';
begin
dbms_scheduler.enable('HR.progfilewatcher');
end;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB_B"',
program_name => '"HR"."PROGFILEWATCHER"',
event_condition => null,
queue_spec => '"SYS"."FILEWATCHER1"',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB_B',
auto_drop => FALSE,
enabled => TRUE);
END;
begin
dbms_scheduler.set_attribute('hr.job_b','parallel_instances',true);
end;
select * from hr.t05317_fw ;

复制代码

select * from dba_scheduler_notifications;
begin
DBMS_SCHEDULER.SET_ATTRIBUTE('hr.job_c', 'raise_events', DBMS_SCHEDULER.JOB_all_events);
end;
select * from dba_scheduler_jobs j where j.job_name='JOB_C' ;
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'hr.job_c',
recipients => 'zhang3@example.com',
events => 'job_all_events');
END;
-----
select s.USERNAME , s.TERMINAL, s.RESOURCE_CONSUMER_GROUP from gv_$session s where terminal='pts/5';
select * from dba_scheduler_job_classes;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB_F"',
job_type => 'PLSQL_BLOCK',
job_action => 'declare
v_1 number;
begin
select count(*) into v_1 from tbig a , tbig b;
end;
',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"JOBCLASS1"',
comments => 'JOB_F',
auto_drop => FALSE,
enabled => TRUE);
END;
grant execute on jobclass1 to hr;
select * from dba_tab_privs tp where tp.grantee='HR';
select * from dba_scheduler_running_jobs;

复制代码