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:
This form is fairly self-explanatory, but a couple of notes:
- No worries, if you get frustrated with the form at some point, check the Advanced box and manually code your interval
- 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.
69 Comments
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
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
I think it’s time to open a service request with My Oracle Support…sounds like you have a database problem.
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.
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
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
SYS.Dba_SCHEDULER_GLOBAL_ATTRIBUTE – you can see and set these in SQLDev using the DBA > Scheduler > Global Attributes page
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?