Start OS jobs from within Oracle
This article will describe how to run OS commands from inside an Oracle database. It requires an Oracle database at least of version 10g.
- Configuring the Scheduler
- Required privileges
- Create an External Job
- Run an Externen Job
Configuring the Scheduler
As described in the Metalink document
some tasks are to fulfill first to get the scheduler ready for "external jobs".
So the configuration file
$ORACLE_HOME/rdbms/admin/externaljob.ora needs some
chown root:oinstall $ORACLE_HOME/rdbms/admin/externaljob.ora chmod 640 $ORACLE_HOME/rdbms/admin/externaljob.ora
Alternatively, the group can also be
dba, but the owner must be
root, no other user must have write permission to this file.
Now to the question: Under which OS user shall our "external jobs" tun? For
security reasons, Oracle recommends an unprivileged user (e.g.
here. But if you trust the users being able to create external jobs
(or the admin is the only one), you may also decide for
So place the user you decided for at the end of the file
run_user = nobody run_group = nobody
Finally, adjust permissions on the file
chown root:oinstall $ORACLE_HOME/bin/extjob chmod 4750 $ORACLE_HOME/bin/extjob
Same as above also applies here: The group may as well be
else has to stay as described.
Generally spoken, above thoughts should apply here as well. But on Windows, the scheduler does not seem to care about those two files at all. Rather, here:
- The service
OracleJobScheduler%ORACLE_SID%must be running
- User (and thus indirectly the group) must be configured via that service (Properties => Log On)
To create an external job, the database user needs a.o. the privileges
CREATE EXTERNAL JOB.
Create an External Job
Now we can create an OS job:
DBMS_SCHEDULER.CREATE_JOB ( job_name => 'my_job', job_type => 'executable', job_action => '/path/to/executable', enabled => true, auto_drop => false );
Pretty self-explaining – but permit a hint:
job_name will be required to
execute the job, so make it short and consise. If you set
auto_drop => true,
the job will automatically be removed immediately after its last execution.
Last execution? Well, above example is minimal. Of course you can specify further settings as e.g. schedules (it's the scheduler, right?). Details can be found in the Oracle documentation.
At least on Windows, one should redirect the output of the executable (e.g.
job_action => 'C:\test.bat >nul'. As soon as output is generated,
the scheduler tends to fail - so this will prevent that failure.
Run an Externen Job
Did you not specify a schedule at creation, or need to run the job out of the schedule, this can be done using the following command:
You may recognize the parameter to the procedure: it is the
at creation time.
Scripts do not necessarily run from within the directory they are located in –
but usually from the root directory
/. If you need the scripts directory, at
least in the Bash it's easy to tell:
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27370: job slave failed to launch a job of type EXECUTABLE ORA-27300: OS system dependent operation:getting external job pid failed with status: 2 ORA-27301: OS failure message: No such file or directory
This usually means you did not follow the steps above, or missed at least a part of it. Check the permissions on both a.m. files, and the configured user as well.
ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect function
In this case there's something wrong with the executed OS command:
- The command may not exist (typo?). The error is not "File does not exist"
when executing a batch file via
job_name=>'C:\Windows\System32\cmd.exe /q /c C:\test.cmd >nul') – since
- The OS command produces output which is not intercepted (with e.g.
>nul– the scheduler does not like anything but an errorlevel=0
ORA-27369: job of type EXECUTABLE failed with exit code: Not owner
ORA-27369: job of type EXECUTABLE failed with exit code: Not owner ORA-06512: at "SYS.DBMS_ISCHED", line 150 ORA-06512: at "SYS.DBMS_SCHEDULER", line 441 ORA-06512: at line 1
A quite common error – its reason is not always easy to tell. "Not owner" does not necessarily relate to the command called itself, but can as well be something the called script is touching. A classic:
Well thought – but in this case unfortunately wrong: it leads directly to
above error, since
$HOME is not defined. Thus the resulting file
/.bashrc, which is not owned by the executing user (in fact, it
probably even does not exist at all - but we all now about the precision of our
Oracle error messages, don't we?).
ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2
For those crazy people who run Oracle on a playstat… ahem, Windows: You need to
OracleJobScheduler%ORACLE_SID% service started.