Today, I had to create a single row from:
Select tablespace_name from dba_tablespaces ;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
This can easily be done with (11.2 and up):
select contents,listagg(TABLESPACE_NAME,',')
within group
(order by CONTENTS) my_tbs
from dba_tablespaces
group by contents;
CONTENTS MY_TBS
--------- ---------------------------------------------
PERMANENT SYSAUX,SYSTEM,USERS
TEMPORARY TEMP
UNDO UNDOTBS1
This works nice!
01 November 2016
29 March 2016
kill repeating job when running longer than n minutes
Some repeating jobs may be running in a loop now and then, or should be killed after N minutes for whatever reason you may think off.
You can raise an event when a scheduled job is running longer than N minutes, but this event has to be handled. Otherwise, the job will just continue to run.
Below a small example how to set this up for MYJOB. Important to run from the job owner!
--connect as jobowner:
conn jobowner/password
-- create a table for output
create table JOB_OUTPUT (
log_date timestamp with time zone
,output varchar2(4000));
-- add an event queue subscriber for this user's messages
exec dbms_scheduler.add_event_queue_subscriber('myagent');
-- set the maximum run time for the jobbegin
dbms_scheduler.set_attribute(name=>'MYJOB',attribute=>'max_run_duration',value=> numtodsinterval(120,'minute'));
end;
/
-- create a 'sniper' job to kill MYJOB - based on eventbegin
dbms_scheduler.create_job
('KILL_MYJOB',
job_type=>'plsql_block',
job_action=>
'insert into job_output values(systimestamp, ''kill_myjob started'');
dbms_scheduler.stop_job(''KILL_MYJOB'',false);',
event_condition =>
'tab.user_data.object_name = ''MYJOB'' and
tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
queue_spec =>'sys.scheduler$_event_queue,myagent',
enabled=>true);
end;
/
-- you can see in the output table if the job was killed:
select * from JOB_OUTPUT;
Subscribe to:
Posts (Atom)