Scheduling Jobs with Oracle SQL Developer

thatjeffsmith SQL Developer 65 Comments

Tell Others About This Story:

Don't judge by it's order in the object tree!

DBMS_SCHEDULER is awesome. It’s flexibility is remarkable. From setting up execution windows to remote execution to automatic notifications, there is a LOT to like. That doesn’t mean it’s perfect. For developers and DBAs, it can be a real pain to type out 2000+ characters to simply create a job. Being able to have such a fine level of control over the execution of a job has led to a very complex mechanism and infrastructure. This complexity, however, really sets the scene for a GUI – a nice visual interface that allows the user to input some information, which then churns out the nasty-looking code for you.

Of course if you enjoy using EM or Grid Control and you’re already there, feel free to use the web forms. But, if you already have SQL Developer spun up, you might as well do it there. I personally think our implementation is better, but please don’t tell anyone I said that 😉

If you haven’t noticed this feature in SQL Developer, I forgive you. It’s at the very end of a very long tree list. If you connect to your database and expand a SCHEMA, you’ll see ‘SCHEDULER’ at the very bottom. In this case, it’s worth the scroll!

There’s a wizard interface that will walk you through the entire process of creating a new job, or you can use the traditional forms to create and alter existing jobs and schedules.

Let’s take a quick look. And by quick, I mean, let’s zoom into one very small aspect of working with the SCHEDULER and SQL Developer –

Defining repeating intervals for your job to execute

The mechanism for defining when your job will execute is nearly identical to setting up a schedule. It’s lurking under the covers. Look for the ‘edit’ button in the job and schedule forms – a la

Clicking the edit button will bring you to a form that looks something like this:

Quickly build your Repeating Interval definition

This form is fairly self-explanatory, but a couple of notes:

  1. No worries, if you get frustrated with the form at some point, check the Advanced box and manually code your interval
  2. Check this to indicate last versus first occurrence, e.g. -1 would be the last day of the month. Handy as each month has a different number of days!

I know some of you will still insist on doing everything by the command-line

And that’s OK (grudging acceptance), but at least spin up SQL Developer to create your scripts. The beauty of these dialogs is that they show the underlying SQL statements so you can easily copy them into your .SQL files.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 65

  1. Hi Jeff,

    Information you have given is awesome! I have a query and I would appreciate if you could help me with it.
    In my project, there are hundreds of tables. I want to take backup of tables which gets updated regularly, many of them updates daily and rest updates on a monthly basis. I want to take backup of tables after they get update.
    Can you propose a way how could I achieve this?

    Looking forward to your reply.

  2. Hello:

    I created a job in SQL Developer 4.1 and scheduled it to run daily at a certain time (not too complicated I guess). After a few initial hiccups, it started to run but only for a few days. After which, it just stopped and never ran again. Upon researching, I found below link and tried to change a few things to see if it can affect the end result… but nothing worked. Can you help me?

    https://community.oracle.com/thread/648581

    Sachin

  3. Hi Smith,

    Where exactly can I find sender’s email address for the Database server.
    I have scheduled the job but it throws error ORA24098 invalid value for email_server.

    Thanks.
    Atul

    1. thatjeffsmith Post
      Author
  4. Hi Jeff,

    That’s really a valuable info.

    I have a generated report and I need to schedule that. Can I do that in SQL Developer?

  5. Can we dynamically assign time to a DBMS_SCHEDULER !!
    I wanted to dynamic time which will come from database. if time is Hourly
    repeat_interval => ‘FREQ=Hourly ; INTERVAL=2’,

    ” Interval ” will come from database column can we achieve same ?

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      In a worksheet, run something like, EXEC dbms_scheduler.run_job(‘myjob’);

      I’ve logged a bug that we don’t have a Run Now context menu item for jobs in the tree.

  6. Hi i want to know how to schedule a sql code, just a code select, drop, create command include code to schedule in sql developer and how to do it.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. i cannot find the aforesaid ‘job type’ in new job wizard.can u please send me the version and the screen shot of the page.

          2. thatjeffsmith Post
            Author
          3. thatjeffsmith Post
            Author

            and what version would you have?

            also, are you using the New Script Job Wizard? – There are TWO dialogs to choose from when creating a new job

          4. my version is 4.1.1.19

            there is no option to select script;only available are PL/SQL block,stored procedure,executable,named program and chain.

          5. thatjeffsmith Post
            Author
          6. My database is 10g,If that is the case is there a way to schedule scripts in sql plus by using sql developer.

          7. great post!

            I just tried setting up a simple job, running a sqlplus script that inserts a random row from one table into another table, that I want to run every 5 minutes. In type of job I chose script, SQLPlus for script type, and typed the insert into table select from… into the dialogue window, then set it up to run every 5 minutes on weekdays.

            It created the job ok, when it first ran it was failing with CREDENTIAL NAME cannot be NULL – even thought I thought this was optional with job running on local database. In any case I created a credential, then edited the job and picked the credential from the drop down and resubmitted the job.

            Now I am getting ORA-27369: job of type EXECUTABLE failed with exit code: The storage control blocks were destroyed errors whenever the job runs. I don’t know why it’s saying job type EXECUTABLE when I clearly specified it was a script job.

            I could just run this from windows scheduler with a command file, but I’ve never run jobs in oracle and would like to get it working.

            What am I missing here?

    1. thatjeffsmith Post
      Author
  7. Ah, resurrecting a two and a half year old post.

    I’m using SQL Developer 4.1.1.19, and mostly, the Scheduler options have worked. But suddenly, when I right click on a Scheduler item (be it a job, a schedule or a program), and select edit, nothing happens. Editing other objects seems to be basically ok.

    Oh, I’m wrong. I can edit programs, but not schedules or jobs. I can select the schedules and jobs, to bring their details into a Worksheet tab, but the edit action still doesn’t work.

    Any idea how I can find out what’s wrong?

    1. thatjeffsmith Post
      Author

      We throw a ton of SQL at the data dictionary to open the Job editor. Do you see anything amiss in the View > Log (Statements | Logging Page) panels?

      Also, can you open the job, and then use the Actions, Edit button on the editor?

      1. Thank you! Your response has fixed the problem! 😉

        I’ve tried editing the schedule, and now, miraculously, it works. When (and if) it fails again, I’ll have a look at the log, and come back.

  8. Hi Jeff,
    I’m searching for a mechanism that’d allow a job to run inside a specific time window, then stop.

    I mean that my job script would frequently ask to the job monitor if the time window is over or not.

    => do as much operation as possible in the (dynamically) defined time window.

    Are you aware of such functionality ?
    With best regards.
    Eric

    1. Funny : I posted the question after 2 days searching … and find the answer a few seconds after posting it !

      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name => ‘.’
      ,attribute => ‘STOP_ON_WINDOW_CLOSE’
      ,value => TRUE);

      1. thatjeffsmith Post
        Author
  9. I apologize if this question is already answered/asked. If so, please point to it.
    What I would like to see is, RMB on the procedue(inside a pkg) and then say create scheduled job. This should pull in the procedure/pkg name with parameters.
    Do you know if this can be achieved with the current versions? I am on 4..0.2.15

    Thanks,

    Sujith

  10. My question is similar to Ramir. In my script, I am trying to do two things: drop and table; recreate the table.
    I am recreating the table because the underlying data sets have been updated. If I want to schedule this to be done every week …what would be the best way to run this script.

    1. thatjeffsmith Post
      Author
  11. Hi Jeff,

    Thanks and I find this really helpful. I was wondering if you can give me some guidance as well – I was wanting to know to schedule an SQL script to run and be able to also output the file into a csv and save it to a specified location.

    Is this doable? Any tips will be much appreciated.

    1. thatjeffsmith Post
      Author

      I would write a stored procedure to build the file for you. You can write to a database directory. Then schedule the stored proc to run – this way everything stays INSIDE the database.

      You CAN tell the db to spawn an OS command/batch script to launch SQL*Plus for example, but that’s not going to scale as well or be as robust.

  12. Hello,

    I would like to know if the scheduler can start a job on every first day of the month, if this is on weekend forward to next monday.

    1. thatjeffsmith Post
      Author
  13. Hi Jeff,

    I was wondering if I can schedule a User Defined report to run when my Oracle SQL Developer client if closed? And/or possibly send the result of the query (chart).

    Best regards,
    Ivan

  14. Hello Jeff,

    I am not able to see any web form in Oracle 11g as you have shown in the screenshot. Is it only available in 11g R2 onwards?

    I want to schedule a procedure for daily run.

    Deeply appreciate your help.

    Best Regards,
    Anoop

    1. thatjeffsmith Post
      Author
  15. How i can send notification mail to my mail box while i am doing a job schedule.I didn’t see any option.Please guide me with screenshots.

    Thanks
    Gyana

  16. Sorry Jeff, i have a another question… Would it be possible to schedule RMAN jobs with SQL Developer? Should i select “type of job” = Executable and then select rman.exe and add all arguments. If so, do you have an example?

    Kind regards, Jan Willem

  17. Hi Jeff,

    when i look at a job, i get “ORA-942 Table or view does not exist” on the tabs Notifications and Dependencies. De other tabs Structure, Details, Job Arguments, Properties and Run log are functioning normally. The user has DBA right…

    Which tables/views are being used for the Notifications and Dependencies?

    Kind regards, Jan Willem

  18. Hi
    I want to schedule the job by hourly basis but from 10 am to 8 pm.
    Please help me where to write the logic.

    Regards
    Bhaskar

    1. thatjeffsmith Post
      Author
  19. Hi Jeff,

    is it possible to generate kind of installtion scripts for scheduler objects which were graphically defined using SQL Developer? I haven’t found anything about this feature…

    Kind regards
    Matthias

  20. Jeff – Thanks for the writeup. Wondering if you can help me – when I select a Job, I get 6 tabs of information. The last tab “Run Log” gives me an error “ORA-00942: table or view does not exist”. Not sure what permission I’m missing. I am able to do a SELECT from the USER_SCHEDULER_JOB_LOG in a separate window without issue.

    I’m running Oracle 11g and Sql Developer 3.1.07 – I’m not able to upgrade at this time..

    1. thatjeffsmith Post
      Author
  21. i have same problem of
    Kamonpan
    Jan 29, 2013 @ 12:41:00

    Sorry for my short msg. My meaning is no form as your picture available, then i cannot define the repeating interval. When i click edit (small pencil icon) nothing happens. I use SQL developer version 3.2.xx

    Thanks for any advise
    i think ever oracle products have some bug in software

    1. thatjeffsmith Post
      Author
  22. Have you experimented with a way of automating SQL Developer processes outside of the database? For example, it is easy to use sqlplus to run a command in a script via sqlplus … @myscript.sql. I want to do something with SQL Developer at the Linux or MS Windows command line. I am using SQL Developer to copy a SQL Sever database table over to Oracle each night with right click on SQL Server table > Copy to Oracle… option. LOL…That’s a lot of clicking that I’d like to do just once. In all my Googling all that I have been able to come up with are articles like this one. Any thoughts or ideas?

    1. thatjeffsmith Post
      Author

      We do have a commmand-line interface that you could try, and some migration stuff is in there – but the ad-hoc ‘copy to Oracle’ for a single Table is not. At least not yet.

      1. Thanks. I found your post on the command-line interface too after you wrote this. I’ve had a pleasant afternoon searching your site! cli gave me a reason to upgrade to 4.x.

        Use case for what I am doing: I don’t like the data move option under the migration tools. I always want a backout plan. I use the copy option for this reason. I have some shell scripts with sqlplus scripts to prepare for the copy operation. I manually perform the copy steps. Since one of the copy operations takes several hours, I have a cron job to run another script at 01:30. Hence, it would be great to have one script to do it all these steps but add a step with a sql developer section to make the db to db copies. I love this aspect of sql developer. As a lazy developer that means I don’t have to write code to make the db to db copy. Oh please please enhance my laziness by making the one off copy operation available to the cli interface!

        1. thatjeffsmith Post
          Author

          Apparently this is pretty easy to build with a java app using JDBC if you’re feeling adventurous. You could always setup a DB_LINK and use a CTAS if you have the the heterogenous stuff licensed as well…

    1. thatjeffsmith Post
      Author

      ‘this job’ – I don’t know what your job is doing. But if it is running a SELECT or similar to a table across the DB_LINK are you referencing the table appropriately?

      Is your job running a stored procedure or script? Can you run that manually to reproduce/test for the ‘does not exist’ error?

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
      1. Sorry for my short msg. My meaning is no form as your picture available, then i cannot define the repeating interval. When i click edit (small pencil icon) nothing happens. I use SQL developer version 3.2.xx

        Thanks for any advise

  23. Finally, wizard for “SCHEDULER” is working.
    Nice addition for the new version.

    I like to execute commands manually from command line so I will certainly use SQL Developer to create scripts.

    Keep up the good work!

    Regards,
    Marko

  24. I liked the information very much. This can make scheduling a job very easy.
    However, I am sorry to say when I expanded my schema I could not find Schedules option,
    infact many other options were missing. Seems like my DB is not enabled with these features.
    Thanks for the information though 🙂

    1. thatjeffsmith Post
      Author

      Every Oracle database comes with scheduling out-of-the-box. What version of Oracle are you connected to? DBMS_SCHEDULER made its debut in 10g.

      Try
      [sql]
      SELECT * FROM user_jobs;

      SELECT * FROM user_scheduler_jobs;
      [/sql]

      Also, what version of SQL Developer are you running?

Leave a Reply

Your email address will not be published. Required fields are marked *