grant create job to <user>;
grant create any job to <user>;
grant CREATE EXTERNAL JOB to <user>;
-- Grant access to oracle home
chmod a+rx /opt
chmod a+rx /opt/oracle
chmod a+rx /opt/oracle/db
chmod a+rx /opt/oracle/db/bin
on os -- 391820.1
sudo su – root
cd <ORACLE_HOME>/rdbms/admin
chmod 640 externaljob.ora
vi externaljob.ora
run_user = oracle
run_group = dba
groups oracle
Verify oracle is in the group that owns the oracle home directory “maybe oinstall”
[/extjob]# ls -l <ORACLE_HOME>/rdbms/admin | grep externaljob.ora
-rw-r--r-- 1 root dba 1531 Aug 8 14:17 externaljob.ora
[/extjob]# ls -l /app/oracle/product/10.2.0.4/bin | grep extjob
-rwsr-x--- 1 root dba 622819 Oct 21 2008 extjob
-rwxr-xr-x 1 oracle dba 622819 Oct 21 2008 extjobo
Example archive script, install this on os
mkdir /extjob/archive –p
chown oracle.dba /extjob –R
chmod 775 /extjob –R
#Note Oracle is fairly touchy about the syntak and I had to play with it a little to set it to get no errors, things that work simply on the os may not work as expected from the oracle user.
#Also remember the user is coming from the oracle home and all references are from oracle home so you may need to declare paths for logic
vi archive.sh
#!/bin/bash
touch /extjob/stj_test3.log
tar -czf /extjob/stj4.tgz -C /extjob stj_test3.log
mv /extjob/stj4.tgz /extjob/archive/stj4.tgz
#Now you plsql application to call archive.sh
# This job is scheduled to run immediately and in the same session if you want it to run in a different session you’ll need to add a run_job with a use_current_session=>false
PROCEDURE ARCHIVE_FILE
AS
BEGIN
begin
dbms_scheduler.drop_job(job_name => 'archivefile2');
exception
when others then
null;
end;
dbms_scheduler.create_job(job_name => 'archivefile2',
job_type => 'executable',
job_action => '/extjob/archive.sh',
enabled => TRUE,
auto_drop => TRUE
);
commit;
exception
when others then
dbms_output.put_line('ERROR:' || sqlerrm);
END ARCHIVE_FILE;