Friday, December 30, 2011

_trace_files_public

In an earlier post, I looked at the tracefile_identifier parameter. This post, tested on an Oracle 9 database, uses it in conjunction with _trace_files_public. First create a trace file without setting the _trace_files_public parameter:
 
  1* alter session set tracefile_identifier = before
SQL> /
 
Session altered.
 
SQL> select value from v$parameter
  2  where name = '_trace_files_public'
  3  /
 
no rows selected
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> select sysdate from dual
  2  /
 
SYSDATE
---------
30-DEC-11
 
SQL> alter session set sql_trace = false
  2  /
 
Session altered.
 
SQL>
 
This creates a trace file with permissions of 640 so, if you are not in the dba group, you will not be able to read it:
 
TEST9 > ls -ltr
total 8
-rw-r----- 1 oracle dba 2303 Dec 30 11:46 mvltst_ora_59105_BEFORE.trc
TEST9 >
 
Set _trace_files_public to true and repeat the test:
 
SQL> alter session set tracefile_identifier = after
  2  /
 
Session altered.
 
SQL> select value from v$parameter
  2  where name = '_trace_files_public'
  3  /
 
VALUE
--------------------
TRUE
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> select sysdate from dual
  2  /
 
SYSDATE
---------
30-DEC-11
 
SQL> alter session set sql_trace = false
  2  /
 
Session altered.
 
SQL>
 
This creates a trace file with permissions of 644, which anybody can read:
 
TEST9 > ls -ltr
total 24
-rw-r----- 1 oracle dba 2303 Dec 30 11:46 mvltst_ora_59105_BEFORE.trc
-rw-r--r-- 1 oracle dba 2302 Dec 30 12:07 mvltst_ora_56368_AFTER.trc
TEST9 >
 
This can be useful on a test machine, where developers want to read their trace files without DBA intervention.

2 comments:

Laurent Schneider said...

alternatively, I used to grant read on the trace directory to the developers...

Andrew Stuart Reid said...

Dear Laurent,
Good to hear from you and I hope you had a good Christmas. We used to do something similar. We had a cron job which ran every 15 minutes changing the permissions of all the trace files in the directory! I didn't think that your idea of granting read access to the directory would help if the files inside had permissions of 640.
Regards
Andrew