1 Reply Latest reply on Aug 4, 2017 4:19 PM by rp0428

    Deadlock on dual when executing a job

    Christian Pitet

      Hi,

       

      I am using SQL Developer to create and execute a job. I am using the wizard. But when I apply the wizard, I get an error ORA-4020 "deadlock detected". My operation is very siùple it is "select sysdate from dual". I don't understand why I get a deadlock error because the object is "dual", not a table or so. Below is the output generated by the assistant.

       

      BEGIN
      DBMS_SCHEDULER.set_attribute( name => '"SIVOA"."TRUNCATE_EV_48H"', attribute => 'job_action', value => 'begin
      select sysdate from dual;
      end;');
              DBMS_SCHEDULER.set_attribute_null( name => '"SIVOA"."TRUNCATE_EV_48H"', attribute => 'start_date');
              DBMS_SCHEDULER.set_attribute_null( name => '"SIVOA"."TRUNCATE_EV_48H"', attribute => 'repeat_interval');
      DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (    
                   job_name => '"SIVOA"."TRUNCATE_EV_48H"'
                   );
      DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (    
                   job_name => '"SIVOA"."TRUNCATE_EV_48H"', 
                   recipients => 'sombody@somecompany.fr',
                   sender => '',
                   subject => 'Oracle Scheduler Job Notification - %job_owner%.%job_name%.%job_subname% %event_type%',
                   body => 'Job: %job_owner%.%job_name%.%job_subname%
      Event: %event_type%
      Date: %event_timestamp%
      Log id: %log_id%
      Job class: %job_class_name%
      Run count: %run_count%
      Failure count: %failure_count%
      Retry count: %retry_count%
      Error code: %error_code
      %Error message: %error_message%
      ',
                   events => 'JOB_STARTED, JOB_BROKEN, JOB_CHAIN_STALLED, JOB_FAILED, JOB_OVER_MAX_DUR, JOB_SCH_LIM_REACHED',
                    filter_condition =>  ''
                   );
      DBMS_SCHEDULER.enable(name=>'"SIVOA"."TRUNCATE_EV_48H"');
      END; 
      /
      

       

      I am using SQL developer version 4.2.0.17.089. And DB is 12.1.

       

      Regards.

        • 1. Re: Deadlock on dual when executing a job

          Wrong forum!

           

          This question has NOTHING to do with sql developer so please mark it ANSWERED and repost it at:

          General Database Discussions

           

          Also you can NOT execute sql statements in pl/sql without using a clause to tell Oracle where to put the data

          1. value => 'begin 
          2. select sysdate from dual; 
          3. end;'); 

          Where do you expect Oracle to put the value of SYSDATE that the query returns?

           

          You need to test your code MANUALLY before you try to automate it. If you need further help repost your question in the proper forum.