_____________________________________________________________________________________ SCHEDULING JOBS WITH ORACLE SCHEDULER: http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN034 ADMINISTERING ORACLE SCHEDULER: http://docs.oracle.com/cd/E11882_01/server.112/e25494/schedadmin.htm#ADMIN035 GENERAL INFO (ALL_SCHEDULER_JOBS): https://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_2046.htm _____________________________________________________________________________________ describe dba_jobs describe dba_scheduler_jobs describe dba_scheduler_running_jobs describe dba_scheduler_job_run_details _____________________________________________________________________________________ exec dbms_scheduler.disable('XXXSCHEMAXXX.XXXJOBNAMEXXX'); exec dbms_scheduler.stop_job(job_name => 'XXXSCHEMAXXX.XXXJOBNAMEXXX'); exec dbms_scheduler.stop_job(job_name => 'XXXSCHEMAXXX.XXXJOBNAMEXXX',force=>true); select 'exec dbms_scheduler.disable(''' || owner || '.' || job_name || ''');' from dba_scheduler_jobs where owner = ''; select 'exec dbms_scheduler.disable(''' || owner || '.' || job_name || '''); ' "DISABLE" from dba_scheduler_jobs where enabled = 'TRUE' order by 1; select 'exec dbms_scheduler.enable(''' || owner || '.' || job_name || '''); ' "ENABLE" from dba_scheduler_jobs where enabled = 'FALSE' order by 1; _____________________________________________________________________________________ DBA_SCHEDULER_JOBS ================== set pages 1000 set lines 200 select owner, job_name, enabled from all_scheduler_jobs where owner = upper('XXXOWNERXXX') order by 1, 2; col owner for a12 col repeat_interval for a75 select owner, job_name, repeat_interval, enabled from dba_scheduler_jobs order by 1, 2; col owner for a12 col repeat_interval for a75 select owner, job_name, repeat_interval, enabled from dba_scheduler_jobs order by 1, 2; column credential_name format a25 column username format a20 column comments format a20 select credential_name, username, comments from user_scheduler_credentials order by credential_name; select job_name from dba_scheduler_jobs order by 1; select job_name from user_scheduler_jobs order by 1; select job_name from user_scheduler_job_run_details order by 1; select status, error#, actual_start_date, additional_info from user_scheduler_job_run_details where job_name = 'jb_hajo'; -- exec dbms_scheduler.purge_log; _____________________________________________________________________________________ oracle:orcl:/home/oracle/hru/tmp$ cat /home/oracle/hru/tmp/shellpgm.ksh #!/bin/ksh cd /tmp date >> /home/oracle/hru/tmp/shellpgm.out /bin/ls -alv >> /home/oracle/hru/tmp/shellpgm.out _____________________________________________________________________________________ !!! TO PREVENT FROM HITTING ORA-27369 - ENSURE THAT THE FOLLOWING VALUES ARE SET !!! !!! TO PREVENT FROM HITTING ORA-27369 - ENSURE THAT THE FOLLOWING VALUES ARE SET !!! !!! TO PREVENT FROM HITTING ORA-27369 - ENSURE THAT THE FOLLOWING VALUES ARE SET !!! login as root (!!!) vi $ORACLE_HOME/rdbms/admin/externaljob.ora --> Change entries: run_user = nobody run_group = nobody --> to: run_user = oracle run_group = oinstall _____________________________________________________________________________________ create user us_hajo identified by hajo default tablespace users temporary tablespace temp; /* exec dbms_scheduler.create_credential('CD_HAJO', 'oracle', 'oracle'); grant execute on cd_hajo to us_hajo; */ grant connect, resource to us_hajo; grant create job to us_hajo; grant create external job to us_hajo; conn us_hajo/hajo -- CREATE JOB begin dbms_scheduler.create_job ( job_name => 'jb_hajo', job_type => 'executable', job_action => '/home/oracle/hru/tmp/shellpgm.ksh', start_date => sysdate+1, auto_drop => false, enabled => true, comments => 'Hajos Testjob' ); end; / show error /* credential_name => 'CD_HAJO', exec dbms_scheduler.set_attribute('jb_hajo', 'credential_name', 'cd_hajo'); exec dbms_scheduler.enable('jb_hajo'); */ -- RUN JOB begin dbms_scheduler.run_job ( job_name => 'jb_hajo' ); end; / show error -- DROP JOB exec dbms_scheduler.drop_job(job_name => 'jb_hajo'); -- CLEANUP conn / as sysdba exec dbms_scheduler.purge_log; exec dbms_scheduler.drop_credential('cd_hajo'); drop user us_hajo cascade;