3 Replies Latest reply: Feb 4, 2013 9:54 PM by Purvesh K RSS

    ORA-29283 invalid file operation

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