ORA-23421: job number xxx is not a job in the job queue

ORA-23421: job number xxx is not a job in the job queue

Oracle job scheduler has the error when tried to remove it.

SQL> exec dbms_job.remove(24);
BEGIN dbms_job.remove(24); END;
*
ERROR at line 1:
ORA-23421: job number 24 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 770
ORA-06512: at "SYS.DBMS_JOB", line 180
ORA-06512: at line 1

Option 1
1) Find the Owner of the job from dbms_job table

select job, schema_user from dba_jobs;
JOB Schema_User
--- -----------
24 HR

2) Log in as schema user and then remove the job.

Conn hr
Enter password:
connected.
select job,schema_user from user_jobs;
JOB Schema_User
--------- -----------
24 HR

exec dbms_job.remove(24);
PL/SQL procedure successfully completed.

Option 2
1) dbms_ijob : Oracle provide this package to handle other users jobs.It manage jobs which scheduled in DBA_JOBS.
SYS/SYSTEM user can use this for managing other user jobs.

SQL> exec dbms_ijob.remove(24);
PL/SQL procedure successfully completed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s