SQL Developer 3.1 Scheduler (DBMS_SCHEDULER) Support
Ever since Oracle 10g introduced a new shceduler, managed by the
DBMS_SCHEDULER package, I've received lots of scheduler related questions. It seems the extra flexibility brought along with it a certain amount of confusion. I've written several articles about the scheduler over the years.
Enterprise Manager has kept up with most of the additional scheduler functionality over time, but the interface leaves a lot to be desired as far as usability is concerned. I quickly find myself switching back to using the
DBMS_SCHEDULER package and
SQL Developer 3.0 introduced support for some of the scheduler functionality, but to be honest I paid it very little attention at the time. Since the release of SQL Developer 3.1 I've been trying to force myself into using the IDE in place of SQL*Plus where possible. This gave me an incentive to look at the scheduler support again, which is the subject of this article.
Let me start by saying, if you are new to the scheduler, you really should read up on the
DBMS_SCHEDULER package (maybe using the articles listed above) as it will help you understand the concepts behind how the scheduler is organized and functions. With that in mind, this article will just point out some of the things I like and dislike about the scheduler support in SQL Developer 3.1.
Where is the Scheduler Functionality
The scheduler functionality is split between two locations. The top-level scheduler information and objects are available from the DBA browser (View > DBA).
The "Global Attributes" and "External Destinations" nodes show read-only information. "Job Classes" can be created here and linked to resource consumer groups from the "Resource Manager" section of the browser, but there doesn't appear to be any way to grant access on job classes to specific users from this interface. This seems a little strange. Why create a job class in the IDE if you immediately have to go to SQL*Plus to run "
GRANT EXECUTE ON job-class-name TO username;"?
Also surprising is the lack of support for window groups and windows. The DBA browser supports resource manager and job classes, so support for window groups and windows seems pretty relevant, since they are tied in closely with the scheduler and resource manager link.
The rest of the scheduler functionality is found under the "Scheduler" node of each individual user.
Right-clicking on the "Jobs" node in the tree gives you the choice of "New Job..." or "New Job (Wizard)..." to create a new job. These two dialogs are so similar I find it hard to believe it is necessary to have both. The dialogs give you most of the commonly used functionality as far as job definitons go.
The options available will change depending on the type of job you are trying to define.
Existing jobs are listed under the "Jobs" tree node. Clicking on on an object causes its details to be displayed in the right-hand pane. Right-click and select the "Edit" menu options and you can amend the definition, or see the PL/SQL used to define the object.
Defining repeat intervals using the calendar syntax seems to confuse people quite a bit. A nice touch is the "Repeat Interval" dialog. When defining repeat intervals for jobs or schedules, you can click the pen icon to display this dialog.
If you work from left to right, you can easily define most simple schedules with little effort. If want to try something a little more complex, check the advanced checkbox for a drop-down list of more specific options. My feeling is, if you are comfortable using the advanced options, it's probably easier to just write the calendar syntax yourself, but I'm used to the calendar syntax, so perhaps others may still prefer the advanced options.
Something that grated on me a couple of times were the browse buttons. When defining an executable job (or a file watcher), you are asked to enter an OS path. You are given a browse button, but this shows the directory structure of the local machine, so unless you are running SQL Developer on the database server itself, the browse button is meaningless, since all paths you will be defining are database server paths, not paths on your local PC.
Programs and Schedules
Both these scheduler objects are relatively simple, so the dialogs to create them are simple too, looking like subsets of what is present on the jobs dialog. As you would expect, the comments I made about OS paths and calendar syntax previously are relevant here also.
Existing programs and schedules are listed under their respective tree nodes. Clicking on on an object causes its details to be displayed in the right-hand pane. Right-click and select the "Edit" menu options and you can amend the definition, or see the PL/SQL used to define the object.
The job chains functionality is really quite neat. It can be a little hard to visualize the events in the chain when looking at a PL/SQL script, but the chain view in SQL Developer is very clear.
There are a couple of little bugs in the job chain support in SQL Developer 3.1 at the time of writing. They are both documented here.
Ignoring the bugs, my biggest issue with the job chain support is lack of a "Show PL/SQL" feature. As you are defining all the individual elements of the chain you can capture the PL/SQL by clicking on the "SQL" tabs in the dialogs, but once they are created there is no more access to these scripts. Most other scheduler objects allow you to right-click and select the "Edit" menu option, giving you access to the "SQL" tab again. This is not so for the job chain functionality. That and the fact that there are so many steps during the creation make it very likely that you will forget to copy some of the PL/SQL and be left with no comeback.
With this in mind, I would currently define all job chains using the PL/SQL API, so I could save my creation script in source control, and just use the SQL Developer interface to view the job chains I've created. With a bit of luck, this situation will change in subsequent versions.
SQL Developer has some support for remote database jobs and remote external jobs. I've written about this functionality previously, but my gut feeling is you should probably avoid these features. If you need this type of remote scheduling, you should probably be using Grid Control (or Cloud Control) to do it, not the regular Oracle scheduler.
SQL Developer continues to grow as a product, making it increasingly useful to both developers and administrators. The addition of the scheduler support in 3.0 is certainly welcome. As a tool for viewing existing scheduler objects it is great. There are some limitations when defining scheduler objects, but for the average user they will probably not notice or care about these. It will be interesting to see how this develops.
For more information see:
- Oracle SQL Developer User's Guide Release 3.1
- All Scheduler Articles
- Scheduler Quick Links : 10gR1, 10gR2, 11gR1, 11gR2, 12cR1, 12cR2
Hope this helps. Regards Tim...