This discussion is archived
5 Replies Latest reply: Jan 25, 2013 1:01 PM by spajdy RSS

Error ORA-01017 happened when dbms_scheduler run a job.

945493 Newbie
Currently Being Moderated
Hi All,

I got a problem when I use dbms_scheduler to run a job. I got Error code 1017 when the job is run by scheduler. Please find my steps below:


Oracle version is : Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

1. Created a job successfully by using the code below:
begin
dbms_scheduler.create_job(
job_name => 'monthly_refresh_elec_splits',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN TRADINGANALYSIS.PKG_IM_REPORTING_ERM.REFRESH_ELEC_SPLITS_TEST; commit; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=monthly;bymonthday=25;byhour=10;byminute=35;bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'monthly_refresh_elec_splits.',
auto_drop => FALSE
);
end;
/

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

2. Got the job run details from talbe user_scheduler_job_run_details after the job is finished:

select * from user_scheduler_job_run_details where job_name = 'MONTHLY_REFRESH_ELEC_SPLITS' order by log_id desc;


LOG_ID     LOG_DATE     OWNER     JOB_NAME     JOB_SUBNAME     STATUS     ERROR#     REQ_START_DATE     ACTUAL_START_DATE     RUN_DURATION     INSTANCE_ID     SESSION_ID     SLAVE_PID     CPU_USED     DESTINATION     ADDITIONAL_INFO

2054804     25/06/2012 10:35:01.086000 AM +10:00     TRADINGANALYSIS     MONTHLY_REFRESH_ELEC_SPLITS          FAILED     1017     25/06/2012 10:35:00.300000 AM +10:00     25/06/2012 10:35:00.400000 AM +10:00     +00 00:00:01.000000     1     1025,37017     129396     +00 00:00:00.030000          ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from NETS
ORA-06512: at "TRADINGANALYSIS.PKG_IM_REPORTING_ERM", line 574
ORA-06512: at line 1

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


3. If I run the job directly the job will be finished successfully.

begin
dbms_scheduler.run_job('monthly_refresh_elec_splits',TRUE);
end;

LOG_ID     LOG_DATE     OWNER     JOB_NAME     JOB_SUBNAME     STATUS     ERROR#     REQ_START_DATE     ACTUAL_START_DATE     RUN_DURATION     INSTANCE_ID     SESSION_ID     SLAVE_PID     CPU_USED     DESTINATION     ADDITIONAL_INFO

2054835     25/06/2012 11:05:38.515000 AM +10:00     TRADINGANALYSIS     MONTHLY_REFRESH_ELEC_SPLITS          SUCCEEDED     0     25/06/2012 11:04:35.787000 AM +10:00     25/06/2012 11:04:35.787000 AM +10:00     +00 00:01:03.000000     1     1047,700          +00 00:00:00.030000


--------------------------------------------------------------------------------------------------------------
Additional Info:

PL/SQL Code in procedure

PROCEDURE Refresh_Elec_Splits_Test IS

BEGIN

--Refresh im_fact_nets_genvol from v_im_facts_nets_genvol in NETS
DELETE FROM IM_FACT_NETS_GENVOL;

--the local NETS_GENVOL table has an additional column providing volume splits by generator and month.
--INSERT INTO IM_FACT_NETS_GENVOL values ('test',sysdate,'test',1,2,3,4,5,6,7);

INSERT INTO IM_FACT_NETS_GENVOL
select ngv.*,
ratio_to_report (net_mwh) OVER (PARTITION BY settlementmonth, state)
gen_percent
from tradinganalysis.v_im_fact_nets_genvol@nets.world ngv;
commit;
END;

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

Does anyone can advice where should I check and how can I solve the problem?

Thanks in advance

Edited by: user13244529 on 24/06/2012 18:33

Edited by: user13244529 on 24/06/2012 18:43
  • 1. Re: Error ORA-01017 happened when dbms_scheduler run a job.
    sb92075 Guru
    Currently Being Moderated
    How to ask question
    SQL and PL/SQL FAQ
  • 2. Re: Error ORA-01017 happened when dbms_scheduler run a job.
    945493 Newbie
    Currently Being Moderated
    Thanks for your advice! I already updated my post.
  • 3. Re: Error ORA-01017 happened when dbms_scheduler run a job.
    star_641522 Newbie
    Currently Being Moderated
    I apologize if you already solved this.. but see Metalink ID 790221.1

    +*<Moderator Edit - deleted contents of MOS Doc - pl do NOT post such content - it is a violation of your Support agreement>*+                                                                                                                                                                                                                                                                                                                                                                                                               
  • 4. Re: Error ORA-01017 happened when dbms_scheduler run a job.
    986537 Newbie
    Currently Being Moderated
    I have been experiencing exactly this problem, and was looking forward to seeing the root cause/resolution, only to find that that had been deleted.

    Could this be restored, please? TIA
  • 5. Re: Error ORA-01017 happened when dbms_scheduler run a job.
    spajdy Pro
    Currently Being Moderated
    >
    2054804 25/06/2012 10:35:01.086000 AM +10:00 TRADINGANALYSIS MONTHLY_REFRESH_ELEC_SPLITS FAILED 1017 25/06/2012 10:35:00.300000 AM +10:00 25/06/2012 10:35:00.400000 AM +10:00 +00 00:00:01.000000 1 1025,37017 129396 +00 00:00:00.030000 ORA-01017: invalid username/password; logon denied
    ORA-02063: preceding line from NETS
    ORA-06512: at "TRADINGANALYSIS.PKG_IM_REPORTING_ERM", line 574
    ORA-06512: at line 1
    >

    You are probably doing refresh of Materialized view. For this it is necessary to create DB link with user and password (CREATE DATABASE LINK <linkname> CONNECT TO <username> IDENTIFIED BY <password> USING 'alias').
    If username is not specified then whenever ORACLE use DB link it try to use username and password you logged in. But in job there is no real logon process. Process used for job is able to run different job for different users, so ORACLE in same way "simulate logon"".