UTL_FILE file creation umask problem :
Usually we see lot of people have faced problem with the utl file generated with 640 permission, which means unix ids belonging to others group or world can not read the files.
There is a very simple way I found out resolve this issue.
There is no need to restart listener. It is not related to with what umask setting listener is started.
login to unix with any of your own user id, no need to login as Oracle software owner oracle user.
just set umask to 022 and run the sqlplus
First I will set umask to 027 and show that it will generate 640 permission
Then when I set umask to 022 , it will generate file with 644 permission
$
$ export ORACLE_SID=TEST
$ export ORACLE_HOME=/oracle/11.2.0
$ export PATH=$PATH:$ORACLE_HOME/bin
umask 027
sqlplus test/test@test
SQL> declare
f1 utl_file.file_type;
begin
f1 := utl_file.fopen('/oracle/test','test1.txt','w');
utl_file.fclose(f1);
end;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.
umask 022
SQL> declare
f1 utl_file.file_type;
begin
f1 := utl_file.fopen('/oracle/test','test2.txt','w');
utl_file.fclose(f1);
end;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.
Following are the files generated in /oracle/test
-rw-r----- 1 orcusa dbausa 0 Jan 28 21:46 test1.txt
-rw-r--r-- 1 orcusa dbausa 0 Jan 28 21:47 test2.txt