I had a requirement recently to truncate a table as our applicaition provider had identified this as the cause of an issue some of our users were experiencing. Fine, but this had to be done when the system was not being used and unfortunately, as much as I love doing what I do, I didn’t fancy staying that late!
With that in mind, I set about creating a DBMS_SCHEDULER job. I chose to create a job in this way as opposed to a DBMS_JOB as I read that from 10gR2 onward DBMS_JOB was being replaced with DBMS_SCHEDULER.
So, without further ado, this is how I created the job…
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => '<Schema Name>.X_TRUNCATE_TABLE'
,start_date => TO_TIMESTAMP_TZ('2012/01/12','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => NULL
,end_date => NULL
,job_class => 'AUTO_TASKS_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => '<Schema Name>.<Package Name>.TRUNCATE_TABLE'
,number_of_arguments => 1
,comments => 'Can be used to truncate any table when supplied with the name. Useful for truncating tables which have to be done out of hours.'
);
END;
To explain the above example, here’s the list of attributes and what they are for:
- job_name – the name used to call the job.
- start_date – when the job will first start. This is in the format of YYYY/MM/DD.
- repeat_interval – not used in the above example as it is executed on an ad-hoc basis.
- end_date – when the job will run for the final time.
- job_class – currently either AUTO_TASKS_JOB_CLASS (for system maintenance) or DEFAULT_JOB_CLASS (for anything else).
- job_type – the program type. Could be PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE or CHAIN.
- job_action – what the job will actually do. In this case, it calls TRUNCATE_TABLE from the specified schema and package.
- number_of_arguments – this can be NULL if none are required but here it is set to 1 as the procedure expects a table name.
- comments – this can be set to NULL or some description for the job can be entered.
It is important to remember to wrap the DBMS_SCHEDULER calls in a BEGIN and END; otherwise an error will be thrown.
To set an argument value for the above job, a call to DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE is required:
BEGIN
SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
( job_name => 'X_TRUNCATE_TABLE'
, argument_position => 1
, argument_value => <'table name'> );
END;
We specify the name of the job and the position of the argument that we are setting the value for. If the procedure has more than one argument then the above block of code can be repeated with the appropriate argument_position set. It is important to check that the arguments being set are in the same order as they are specified in the procedure itself.
The next step is to enable the job:
BEGIN
DBMS_SCHEDULER.ENABLE('X_TRUNCATE_TABLE');
END;
If required, the job can be executed straight away:
BEGIN
DBMS_SCHEDULER.RUN_JOB('X_TRUNCATE_TABLE');
END;
Any of the attributes of the job can be easily modified by calling the SET_ATTRIBUTE procedure. For example, to change the start_date for the X_TRUNCATE_TABLE job:
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'NORFOLK.X_TRUNCATE_TABLE'
,attribute => 'START_DATE'
,value => TO_TIMESTAMP_TZ('2012/01/13 19:00:00.000000 +00:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'));
END;
Specify the name of the job to be amended, the attribute you wish to amend and then the new value.