This discussion is archived
3 Replies Latest reply: Feb 4, 2013 7:54 PM by Purvesh K RSS

ORA-29283 invalid file operation

Verdi Newbie
Currently Being Moderated
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
  • 1. Re: ORA-29283 invalid file operation
    Purvesh K Guru
    Currently Being Moderated
    Verdi wrote:
    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


    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).
    Thanks for posting version alongwith other details.

    TO my knowledge, No you cannot.

    Privileges acquired via a Role are not valid in PL/SQL. You need to have explicit privileges.
  • 2. Re: ORA-29283 invalid file operation
    Verdi Newbie
    Currently Being Moderated
    You mean that only READ and WRITE on the directory will work for my user?

    But why then the other user with the different roles is able to read from the directory (created the same way for reading trace files)?

    Sorry, but I'm baffled
  • 3. Re: ORA-29283 invalid file operation
    Purvesh K Guru
    Currently Being Moderated
    Verdi wrote:
    You mean that only READ and WRITE on the directory will work for my user?

    But why then the other user with the different roles is able to read from the directory (created the same way for reading trace files)?

    Sorry, but I'm baffled
    As I said earlier, privileges acquired via Role are not valid in PL/SQL. You need explicit grants.

    It might be that other User has those grants which are not present for you.

    Perhaps, check the DBA/ALL/USER_TAB_PRIVS for both the users and verify the differences of Grants.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points