Of course, you are right. I should have mentioned that in my previous response. Oracle also respects the umask of server process creating the trace file, and No, there is no way to work around that. The umask may be inherited from the listener, from the user's shell, or from the shell that started up the instance, depending on the circumstances.
Thank you for your help. I used your script to check the parameter value. I did set _trace_files_public to TRUE. I have two machines, one worked and the other not. I guess the problem was caused by umask. The umask in the worked machine is 22 and in the not worked machine is 27. If it is right, is there any way in which I don't need to change the umask to make the trace files accessable to everybody?
It should work. Maybe you started up the instance with a different init.ora file than you think. You can use the APT script all_parameters.sql to check the parameter value. Otherwise, you may be looking at an old trace file. Oracle will append to an existing trace file of the intended name, rather than deleting and re-creating it, so the old permissions on the trace file would persist. Also be aware that after sql_trace is set to TRUE in an active session, nothing will be written to the trace file until a new call is started. You can watch the user calls statistic in V$SESSTAT to see when that happens.
(1) shutdown the database,
(2) add _trace_files_public = TRUE to the initial file,
(3) start the database,
(4) use dbms_system.set_sql_trace_in_session to trace a specific session.
I found the privilege of the trace still was -rw-r-----. Is there anything wrong?
It is a hidden and undocumented parameter, but it works. Just set it in init.ora and restart the instance.
I can't find _trace_files_public parameter in the documents and by using following query:
select name from v$parameter where name like '%trace%';
Could you tell me how to use this parameter?
What about setting the _trace_files_public parameter to TRUE?
I want to find a way in which a user who can access to SYSTEM, but is not an OS user in DBA group can find out what SQL statements run behind an application. The SQL_TRACE that we know generates a trace file that can be accessed by only OS users in the DBA group. I understand that changing the trace file priviledge allows other users to access the trace files. However, if I can find someway to let the users not in DBA group to do SQL trace by themselves, that will benefit our business.