_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.