用oracle 10GR2的sheduler定期执行shell脚本的例子

用oracle 10GR2的sheduler定期执行shell脚本的例子
   从oracle10g开始,oracle增加了sheduler，并建议取代原先的dbms_jobs，功能得到及大增强，其中就可以定期执行shell脚本，不在依赖操作系统的crontabl，下而举个例子：

环境：LINUX AS4U4  64BIT
DB版本:oracle 10.2.0.4
1.创建shell
more /tmp/test.sh
#!/bin/sh
date >>/tmp/test.log
hostname >>/tmp/test.log

2.修改shell执行用户.
  即指定数据库用那个用户来执行shell，默认为nobody:nobody，在这一步浪费了我很多时间，你需要把nobody用户生效，linux默认情况nobody用户是禁用的。后来都查到可以修改执行用户
  vi $ORACLE_HOME/rdbms/admin/externaljob.ora
  #run_user = nobody
  run_user = oracle
  #run_group = nobody
  run_group = oinstall

3.在db中以sys用户创建job
exec DBMS_SCHEDULER.CREATE_JOB (job_name=>  'myj_test1', job_type=>'EXECUTABLE',job_action=>'/tmp/test.sh',repeat_interval=>'FREQ=minutely;INTERVAL=1',comments=>'myj_test');

注意：调用shell的job最好对sys用户去创建，如果对普通用户却创建，需可以改一堆文件的权限(参考)
- rdbms/admin/externaljob.ora file must must be owned by root:oraclegroup and be writable only by the owner i.e. 644 (rw-r--r--) It must contain at least two lines: one specifying the run-user and one specifying the run-group.
- bin/extjob file must be also owned by root:oraclegroup but must be setuid i.e. 4750 (-rwsr-x---)
- bin/extjobo should have normal 755 (rwxr-xr-x) permissions and be owned by oracle:oraclegroup

4.排错
  通过dba_SCHEDULER_JOB_RUN_DETAILS的ADDITIONAL_INFO字段查询错误原因

常见错误：
Internal Error numbers for UNIX on 10.2.0.2 or 10.1.0.6 or higher
If you are not using a credential and are using version 10.2.0.2 or higher or 10.1.0.6 or higher you may come across an internal error number. Here are the meanings for the internal error numbers.
274661 - can't get owner of or permissions of externaljob.ora file
274662 - not running as root or externaljob.ora file is writable by group or other or externaljob.ora file not owned by root (can't switch user)
274663 - setting the group or effective group failed
274664 - setting the user or effective user failed
274665 - a user or group id was not changed successfully
274666 - cannot access or open externaljob.ora file
274667 - invalid run_user specified in externaljob.ora file
274668 - invalid run_group specified in externaljob.ora file
274669 - error parsing externaljob.ora file
274670 - extjobo is running as root user or group

exec DBMS_SCHEDULER.ENABLE(name=>'myj_test1');
exec DBMS_SCHEDULER.DISABLE(name => 'myj_test1');
exec  DBMS_SCHEDULER.drop_job(job_name=>'myj_test1');
DBMS_SCHEDULER.INSTANCE