Why DBMS_Scheduler job failed?
I have written one procedure and one job. From job I am running procedure. Following is the script for creating job
DBMS_SCHEDULER.create_job (job_name => 'IBPROD2.RUN_FETCH_ACCT_ALERTS',
job_type => 'STORED_PROCEDURE',
job_action => 'FETCH_ACCT_ALERTS',
start_date => sysdate,
repeat_interval => 'FREQ=HOURLY;INTERVAL=2;',
enabled => TRUE,
auto_drop => FALSE
);
After creating job I am running following command to get job details for owner IBPROD2 where I can see failure_count column value as 1 for RUN_FETCH_ACCT_ALERTS job. There is no problem in procedure FETCH_ACCT_ALERTS when I run it manually.
Can anyone help me in why the job is failing? Am I missing something?
Answer
Query the ALL_SCHEDULER_JOB_RUN_DETAILS view (or perhaps the DBA equivalent).
select *
from all_scheduler_job_run_details
where job_name = 'IBPROD2.RUN_FETCH_ACCT_ALERTS'
You'll be particularly interested in the error# which will give you an Oracle error number you can look up. Also, the additional_info column might have some, er, additional info.
The error code means this:
ORA-28179: client user name not provided by proxy
Cause: No user name was provided by the proxy user for the client user.
Action: Either specify a client database user name, a distinguished name or an X.509 certificate.
So it's something to do with your security set up. Authentication is failing for reason. As I lack a detailed knowledge of your architecture (and I'm not a security specialist) I'm not in a position to help you.
Because I have already created many jobs to run different procedures with same owner. All are running successfully.
So in what way does this procedure differ from all the others?