Add / Delete from dba_jobs

July 30, 2012 admin Old but still valuable, Oracle

You should not delete from dba_jobs; you should use the dbms_job interface to remove the job. Note 61730.1 is a good overview of this interface to the job queue. I also put together a quick example
of submitting and deleting a job:

SQL> variable v_jobnum number;

SQL> exec DBMS_JOB.SUBMIT (:v_jobnum, ‘DBMS_UTILITY.ANALYZE_SCHEMA(”SYS”,”COMPUTE”);’, tr
unc(sysdate) + 1, ‘sysdate + 1’);

PL/SQL procedure successfully completed.

SQL> set head off
SQL> select * from dba_jobs;

1 SYS SYS
SYS 21-DEC-06
00:00:00 0 N
sysdate + 1

DBMS_UTILITY.ANALYZE_SCHEMA(‘SYS’,’COMPUTE’);
NLS_LANGUAGE=’AMERICAN’ NLS_TERRITORY=’AMERICA’ NLS_CURRENCY=’$’ NLS_ISO_CURRENC
Y=’AMERICA’ NLS_NUMERIC_CHARACTERS=’.,’ NLS_DATE_FORMAT=’DD-MON-RR’ NLS_DATE_LAN
GUAGE=’AMERICAN’ NLS_SORT=’BINARY’
0102000202000000 0

COMMENT: The first column of dba_jobs is “JOB”, which is 1 in this case (used for remove).

SQL> exec dbms_job.remove(1);

PL/SQL procedure successfully completed.

SQL> select * from dba_jobs;

no rows selected

Kernel Architecture? Kernel version? Linux RedHat Release? COLD BACKUP