From: Ethan Post <post.ethan_at_gmail.com>
Date: Mon, 22 Dec 2008 13:06:24 -0600
Message-ID: <357b48a90812221106w48e9057dg6c858bcd0da5e17a@mail.gmail.com>

Seeing something a bit odd. I have a series of jobs which use DBMS_JOB, just migrated to 10G. The jobs kick off and attempt to run every 10 minutes until they are table to complete. There are some dependencies which they wait on. Trouble is that they are logging ORA-29283. However, if I log in as the user who owns the job and run DBMS_JOB.RUN to run the job they finish fine and report no error.

29283, 00000, "invalid file operation"

// *Cause:  An attempt was made to read from a file or directory that does

// not exist, or file or directory access was denied by the
// operating system.
// *Action: Verify file and directory access privileges on the file system,
// and if reading, verify that the file exists.
This is the line that either does or does not generate the error.

t_filehandle := utl_file.fopen(p_filelocation, p_filename || v_fileextension, v_filemode);

The variables are all specified in either the package header or the procedure. This particular package happens to exist in another schema but the job user has execute on it. All users also have execute on UTL_FILE.

Just to summarize this is the exact same job id, but it does not work when run by the job queue process and does work when run using DBMS_JOB to run the exact same job from the command line. I am stumped. The file is being opened for file mode 'R'. Here are a few of my current guesses.

  1. Need to switch to using DIRECTORY object, this might be a bug in 10G.
  2. Too many job queue processes, they are all firing at the exact same time and multiple procedures in the same package are all trying to access the same file, perhaps some sort of very hard to recreate locking issue.

If anyone has seen anything like this please let me know.


Received on Mon Dec 22 2008 - 13:06:24 CST

Original text of this message