建立定時任務(Jobs） for oracle

create or replace procedure smp_d as
begin
Delete from sfcs_defects_backup where
org_id=51 and i_timeCommit;
end;

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'smp.smp_d;'
,next_date => to_date('22/03/2011 12:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate+1'
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

SQL> select job,next_date,next_sec,failures,broken from user_jobs;

JOB NEXT_DATE NEXT_SEC FAILURES B
---------- ------------------- ------------------------ ---------- -
42 2011-03-22 12:00:00 12:00:00 N

begin
dbms_job.run(42);
end;

begin
dbms_job.remove(42);
end;

[@more@]

--------------------------
crontab 執行：
00 12 * * * /d.sh.sh 2>&1

d.sh:

#!/bin/sh
ORACLE_BASE=/ora102;export ORACLE_BASE
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
cd /ora102/scripts
sqlplus -s ' / as sysdba' @d.sql
exit

spool d.log
Delete from sfcs_defects_backup where org_id=51 and i_timeCommit;
spool off

--------------------------------------