Friday, July 5, 2013

Remove job from Oracle

There are few types of jobs:
till Orecle10 - packages DBMS_JOB is used.
from Oracle11 - package DBMS_SCHEDULER is used.

DBMS_JOB are used numbers for identifier.
DBMS_SCHEDULER use valid oracle object name (that can not be started from numbers) 
Links to spec: dbms_job is deprecatedschedulerdbms_joball_jobs.

when you try to remove old job by new package you got (2364 is job identifier):

Error starting at line 1 in command:
BEGIN
  DBMS_SCHEDULER.DROP_JOB('2364');
END;
Error report:
ORA-20001: comma-separated list invalid near ,
ORA-06512: at "SYS.DBMS_UTILITY", line 236
ORA-06512: at "SYS.DBMS_UTILITY", line 272
ORA-06512: at "SYS.DBMS_SCHEDULER", line 623
ORA-06512: at line 2 



From SQLDeveloper I got different error when use old package DBMS_JOB:
DBMS_JOB.remove(2364);

[ERROR] ORA-23421: job number 2364 is not a job in the job queue
[ERROR] ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
[ERROR] ORA-06512: at "SYS.DBMS_IJOB", line 770
[ERROR] ORA-06512: at "SYS.DBMS_JOB", line 180
[ERROR] ORA-06512: at line 2

But it was ok from sqlplus under owner user :

BEGIN DBMS_JOB.remove(2364); END; commit;

No comments:

Post a Comment