exec dbms_scheduler.disable('test_scj', true)
exec dbms_scheduler.stop_job('test_scj')
exec dbms_scheduler.drop_job('test_scj')
exec dbms_scheduler.drop_program('test_scp')
exec dbms_scheduler.drop_resource('test_scr')
exec dbms_aqadm.stop_queue('test_q')
exec dbms_aqadm.drop_queue('test_q')
exec dbms_aqadm.drop_queue_table('test_qt')
create or replace type msg_t as object (
id int);
exec dbms_aqadm.create_queue_table('test_qt', 'msg_t', multiple_consumers => true)
exec dbms_aqadm.create_queue('test_q', 'test_qt')
exec dbms_aqadm.start_queue('test_q')
drop table t
create table t(id int, insert_date date default sysdate)
create or replace procedure p(msg msg_t)
is
begin
dbms_session.sleep(5);
insert into t(id) values (msg.id);
end;
exec dbms_scheduler.create_program(
'test_scp',
'stored_procedure',
'p',
1,
false)
exec dbms_scheduler.define_metadata_argument(
'test_scp',
'event_message',
1,
'msg')
exec dbms_scheduler.create_job(
job_name => 'test_scj',
program_name => 'test_scp',
queue_spec => 'test_q',
enabled => false)
exec dbms_scheduler.set_attribute('test_scj', 'parallel_instances', true)
exec dbms_scheduler.create_resource('test_scr', 3)
exec dbms_scheduler.set_resource_constraint('test_scj', 'test_scr', 1)
exec dbms_scheduler.enable('test_scj')
declare
enq_opts dbms_aq.enqueue_options_t;
msg_props dbms_aq.message_properties_t;
msg_id raw(16);
begin
for i in 1..10
loop
dbms_aq.enqueue('test_q', enq_opts, msg_props, msg_t(i), msg_id);
end loop;
commit;
end;
exec dbms_session.sleep(3)
col action for a30
select sid, action from v$session where module='DBMS_SCHEDULER'
select status, resource_units, units_used, jobs_running_count from user_scheduler_resources where resource_name='TEST_SCR'