Oracle Database Discussions

NLSRTL 10.2.0.5.0 Production
Oracle Database 10g Enterprise Edition 10.2.0.5.0 64bi
PL/SQL 10.2.0.5.0 Production
TNS for IBM/AIX RISC System/6000: 10.2.0.5.0 Productio

I am trying to get the content of a trace file generated for me.
Because I don't have privileges to log on the server and copy the trace file for me directly with some os user, I am doing the following:

1. I alter my session trace identifier to easier identify the trace file

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'Func01';

2. I invoke DBMS_MONITOR

3. I run the procedure I want to monitor.

4. I disable the monitoring by calling DBMS_MONITOR

5. At this point I run the following query to identify my trace file:

select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File"
from V$PARAMETER u_dump
cross join V$PARAMETER instance
cross join V$PROCESS
join V$SESSION on v$process.addr = V$SESSION.paddr
where 1=1
   and u_dump.name = 'user_dump_dest'
   and instance.name = 'instance_name'
   and V$SESSION.audsid=sys_context('userenv','sessionid');

It gives me: /ORACLE/MYDB/trace/MYDB_ora_3616822_Func01.trc

I have created directory in advanced on the path where the traces are stored:

CREATE OR REPLACE DIRECTORY trace_dir AS '/ORACLE/MYDB/trace/';

SELECT * FROM dba_directories WHERE directory_name = 'TRACE_DIR';

Output:
OWNER DIRECTORY_NAME DIRECTORY_PATH
SYS	TRACE_DIR	 /ORACLE/MYDB/trace/

I don't have rights to grant read, write on TRACE_DIR to my user, as I am not logged with SYS.

I created a table to store in it the lines from the trace file:

CREATE TABLE tmp_traces_tab
(
  callnum NUMBER,
  line NUMBER,
  fileline CLOB
);

Then I run the following PL/SQL block to retrieve the content of the trace and store it in the table T:

DECLARE
  l_file            UTL_FILE.file_type;
  l_location     VARCHAR2 (100) := 'TRACE_DIR';
  l_filename    VARCHAR2 (255) := 'MYDB_ora_3616822_Func01.trc';
  l_text           VARCHAR2 (32767);
  l_line           NUMBER := 1;
  l_call           NUMBER := 1;
BEGIN

  -- Open file.
  l_file := UTL_FILE.fopen (l_location, l_filename, 'r', 32767);
  
  -- Read and output first line.
  UTL_FILE.get_line (l_file, l_text, 32767);
  
  INSERT INTO tmp_traces_tab (callnum, line, fileline) VALUES (l_call, l_line, l_text);
  l_line := l_line + 1;
  
  BEGIN
  
    LOOP
    
      UTL_FILE.get_line (l_file, l_text, 32767);
      
       INSERT INTO tmp_traces_tab (callnum, line, fileline) VALUES (l_call, l_line, l_text);
       l_line := l_line + 1;
       
    END LOOP;
    
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;
  
  INSERT INTO tmp_traces_tab (callnum, line, fileline) VALUES (l_call, l_line, l_text);
  l_line := l_line + 1;

  UTL_FILE.fclose (l_file);

END;
/

And when I run the code I get the error: ORA-29283 invalid file operation.

Is it possible to a role my user to be able to get the content of the trace files in the directory TRACE_DIR without having explicit READ , WRITE privileges on it?

My user currently has these roles:

select * from dba_role_privs where grantee = USER;

Output:

U1	OPR_ROLE_LOSS_SNAPSHOT_READER	YES	YES
U1	RESOURCE	NO	YES
U1	CONNECT	NO	YES
U1	DBA	NO	YES
U1	OPR_ROLE_SUPPORT_USER	YES	YES

I know that on another db with different user I hit no errors when doing completely the same (of course the program unit I monitor is different).

That other user with which I have NO issues has these roles:

select * from dba_role_privs where grantee = USER;

Output:

U2    DBA    NO    YES
U2    EXEC_SYS_PACKAGES_ROLE    NO    YES
U2    EXECUTE_CATALOG_ROLE    NO    YES
U2    CONNECT    NO    YES