ORA-29283: invalid file operation using UTL_FILE.FREMOVE

I’ve been breaking my head over a problem I had with the UTL_FILE.FREMOVE command trying to remove a file created by another (not oracle) process. On *nix command line, logged in as user oracle (same as database) I’m able to remove the file using the “rm” command, but when using UTL_FILE.FREMOVE it comes back with ORA-29283.

Sample of directory and file permission for this article:

drwxrwsr-x   oracle:oinstall   /var/import

Content of /var/import:
rw-r--r--    grid:oinstall     something.tst

The first thing I thought about, was that Oracle was looking at the privileges of the file (which shows that the group oinstall only is allowed to READ the file) before it would remove it, but the Oracle documentation clearly states that:

The FREMOVE procedure does not verify privileges before deleting a file. The O/S verifies file and directory permissions.

Because on the directory level oracle (through being part of the oinstall group) is allowed to remove files from the directory, as is also proved by the “rm” command, and UTL_FILE.FREMOVE does not verify the permissions, UTL_FILE.FREMOVE should be able todo so too. So why is the command failing?

Quick answer: Oracle actually does some sort of a permission check before removing the file!

Using the “truss” command on an Oracle session using UTL_FILE.FREMOVE showed me that Oracle is executing the following system calls when removing a file with UTL_FILE.FREMOVE:

1.  Get file attributes (that does not include permissions)
stat(“/var/import/something.tst”, ………….
lstat(“/var/import/something.tst”, ………….

2.  Open the file using file access modes for writing and appending
open(“/var/import/something.tst”, O_WRONGLY|O_APPEND)

3.  Get information about the open file descriptor
fstat(……..)

4.  Close the open file descriptor
close(……..)

5.  Actually remove the file
unlink(“/var/import/something.tst”)

The problem lies in steps 2, 3 and 4, where Oracle just tries to open the file you want to remove for writing, which fails because oracle is not allowed to write to the file (on the file level). In my opinion Oracle actually does check permissions by opening the file for writing and immediately closing it.

Conclusion:

If you want to be able to use UTL_FILE.FREMOVE, make sure you have rw (READ/WRITE) access DIRECTLY on the file. Solutions (maybe not applicable in your environment) might be changing the umask or using ACL’s, otherwise some external solutions should be used.

This entry was posted in Database and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

Blue Captcha Image
Refresh

*