I have created oracle directory (SIMPLEDIR) and it points to a physical location on the disk. Oracle version is 12C.
The user has 'CREATE ANY DIRECTORY' and 'DROP ANY DIRECTORY' grants as confirmed by using query
SELECT 'DIR' gtype, PRIVILEGE
FROM sys.dba_sys_privs
WHERE grantee = 'MY_TEST_USER1';
Can also confirm that the use has rights to create and read files.
SELECT *
FROM all_tab_privs
WHERE table_name = 'SIMPLEDIR';
Now I use the following block to create a file on disk
declare
fHandle UTL_FILE.FILE_TYPE;
begin
fHandle := UTL_FILE.FOPEN('SIMPLEDIR', 'my_test_1.txt', 'w');
UTL_FILE.PUT_LINE(fHandle, 'This is the first line');
UTL_FILE.PUT_LINE(fHandle, 'This is the second line');
UTL_FILE.PUT_LINE(fHandle, 'This is the third line');
UTL_FILE.FCLOSE(fHandle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' || SQLERRM);
RAISE;
end;
This works fine and can confirm the file is now on the disk and I can also read the file using a simillar script as above. However when I try to delete the file using the below script I get an error.
declare
fHandle UTL_FILE.FILE_TYPE;
begin
utl_file.fremove('SIMPLEDIR', 'my_test_1.txt');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' || SQLERRM);
RAISE;
end;
The error message is as follows
Error report:
ORA-29291: file remove operation failed
ORA-06512: at line 8
29291. 00000 - "file remove operation failed"
*Cause: A file deletion attempt was refused by the operating system.
*Action: Verify that the file exists and delete privileges granted on
the directory and the file.
It is not a file lock issue since if I create a file and copy to the same location manually, still it fails to delete. How can I get more detail on the exact cause of the error.
To me it looks like a OS level issue since the error message has 'A file deletion attempt was refused by the operating system.'.
This works fine in internal systems where DB is hosted in windows server, but fails on customer where the DB is hosted on Linux.
