1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to create the trace file and how to read that file?

Discussion in 'Oracle Apps Technical' started by SAADIQ, Sep 21, 2015.

  1. SAADIQ

    SAADIQ Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    100
    Hi,
    i have few question
    1)in which location the trace file is create(can i get the location by using
    select name, value
    from v$parameter
    where name = 'user_dump_dest';)

    2)how to convert the trace file to readable format (consultant can do are do we need to send the trace file name to dba he can convert and give the readable file to us)


    Thank u.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    for example :

    Code (SQL):
    SET echo off
    SET serveroutput ON
    var trc_file varchar2;
    var trc_dir  varchar2;

    BEGIN
        EXECUTE immediate q'{select  value from   v$diag_info where  name = 'DEFAULT Trace File'}' INTO :trc_file;
        EXECUTE immediate q'{select value from  v$parameter p where p.NAME=lower('user_dump_dest')}' INTO :trc_dir;
    END;
    /

    spool e:\trc_file.trc
    DECLARE
      l_file utl_file.file_type;
      l_text varchar2(32767);
    BEGIN

      l_file := utl_file.fopen('your trc dir name', regexp_substr(:trc_file,'[^/]+$',1,1), 'r', 32767);
      BEGIN
        loop
          utl_file.get_line(l_file, l_text);
          dbms_output.put_line(l_text);
        END loop;
      exception
        WHEN no_data_found THEN
          NULL;
      END;
      utl_file.fclose(l_file);
    exception
      WHEN others THEN
        dbms_output.put_line('ERROR: ' || SQLERRM);
        IF utl_file.is_open(l_file) THEN
          utl_file.fclose(l_file);
        END IF;
    END;
    /
    spool off

     
    Code (SQL):
    DECLARE
        v_file_name varchar2(4000 CHAR);
     
        v_ident varchar2(4000 CHAR);
     
        v_tmp varchar2(4000 CHAR);
     
        v_clob CLOB;
     
        v_bfile        bfile;
        v_dest_offset  INTEGER;
        v_src_offset   INTEGER;
        v_lang_context INTEGER;
        v_warning      INTEGER;
        v_file_name varcahr2(4000);
    BEGIN
        -- set param
    SELECT
      substr(regexp_replace(VALUE, '(.*\\)(.*)', '\2'), instr(VALUE, '/', -1) + 1)
      INTO
      v_file_name
      FROM
      v$diag_info
      WHERE
      name = 'Default Trace File';


        v_bfile := bfilename('UDUMP_DIR', v_file_name);
        v_dest_offset := 1;
        v_src_offset := 1;
        v_lang_context := dbms_lob.default_lang_ctx;

        -- convert
        dbms_lob.CreateTemporary(v_clob, FALSE, dbms_lob.CALL);
        dbms_lob.FileOpen(v_bfile);

        dbms_lob.LoadClobFromFile
        (
            dest_lob     => v_clob,
            src_bfile    => v_bfile,
            amount       => dbms_lob.lobmaxsize,
            dest_offset  => v_dest_offset,
            src_offset   => v_src_offset,
            bfile_csid   => dbms_lob.default_csid,
            lang_context => v_lang_context,
            warning      => v_warning
        );

        dbms_lob.FileClose(v_bfile);
     
        dbms_output.put_line(v_clob);

        --!! return v_clob;
    END;
     
    Last edited: Sep 21, 2015