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.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

69 Comments

  1. Hello Jeff. Thank you for this useful tip. I have created a procedure and scheduled it to run it. However, I want it to send me an email when the job is complete and/or whenever it fails for whatever reason(s). How can I do that in SQL Developer v. 18.4? Can you please advise. Thanks. HD

    • thatjeffsmith

      notifications via email are built into the scheduler, open the scheduler new job wizard, it’s like step 4.

    • I was able to get to the notifications to enter email for both sender and receiver but got the below when press on Apply.

      Failed to process SQL command
      – ORA-04020: deadlock detected while trying to lock object SYS.SCHEDULER$_EVENT_QUEUE
      ORA-06512: at “SYS.DBMS_ISCHED”, line 7761
      ORA-06512: at “SYS.DBMS_SCHEDULER”, line 4063
      ORA-06512: at line 6

    • thatjeffsmith

      I think it’s time to open a service request with My Oracle Support…sounds like you have a database problem.

  2. Chandra Prakash Malviya Reply

    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.

  3. 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

  4. 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

    • thatjeffsmith

      SYS.Dba_SCHEDULER_GLOBAL_ATTRIBUTE – you can see and set these in SQLDev using the DBA > Scheduler > Global Attributes page

  5. 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?

Write A Comment