7 Replies Latest reply: Aug 25, 2012 2:31 PM by Hussein Sawwan-Oracle RSS

    Unable to generate trace in Oracle11gR2

    user3308982
      Hi


      We are using AIX 5.3 OS and Database version 11.2.0.2

      i am unable to generate trace file




      Thanks
        • 1. Re: Unable to generate trace in Oracle11gR2
          sb92075
          user3308982 wrote:
          Hi


          We are using AIX 5.3 OS and Database version 11.2.0.2

          i am unable to generate trace file




          Thanks
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ




          I can generate trace file by issuing SQL below

          ALTER SESSION SET SQL_TRACE=TURE;
               
               
               
          Handle:     user3308982
          Status Level:     Newbie
          Registered:     Apr 5, 2009
          Total Posts:     293
          Total Questions:     135 (118 unresolved)
          why do you waste time here when you rarely get answer to your questions
          • 2. Re: Unable to generate trace in Oracle11gR2
            sybrand_b
            'My car is broke, please fix my car'
            This is not a chatroom, and we can not see what you do.

            So
            - how are you generating the trace file
            - Where are you looking for the trace file.

            ----------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: Unable to generate trace in Oracle11gR2
              user3308982
              Hi


              we are trying to generate trace from Application (Concurrent Program) and followed doc id -How To Trace a Concurrent Request And Generate TKPROF File [ID 453527.1].

              and at database level i changed parameter value to sql_trace = true and bounced DB still it is not generating file

              Below query showing location for tace


              SELECT 'Request id: '||request_id ,
              'Trace id: '||oracle_Process_id,
              'Trace Flag: '||req.enable_trace,
              'Trace Name:
              '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
              'Prog. Name: '||prog.user_concurrent_program_name,
              'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
              'Status : '||decode(phase_code,'R','Running')
              ||'-'||decode(status_code,'R','Normal'),
              'SID Serial: '||ses.sid||','|| ses.serial#,
              'Module : '||ses.module
              from fnd_concurrent_requests req, v$session ses, v$process proc,
              v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
              fnd_executables execname
              where req.request_id = &request
              and req.oracle_process_id=proc.spid(+)
              and proc.addr = ses.paddr(+)
              and dest.name='user_dump_dest'
              and dbnm.name='db_name'
              and req.concurrent_program_id = prog.concurrent_program_id
              and req.program_application_id = prog.application_id
              --- and prog.application_id = execname.application_id
              and prog.executable_application_id = execname.application_id
              and prog.executable_id=execname.executable_id;


              i tried to search location from above query but unfortunately i didn't find trace file.


              Thanks
              • 4. Re: Unable to generate trace in Oracle11gR2
                mseberg
                Hello;

                I would try this at the session level.
                ALTER SESSION SET sql_trace = true;
                How to Create a SQL Trace from Another Session [ID 100883.1]

                How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues [ID 376442.1]

                How To Enable/Disable SQL Tracing Without Wasting Disk Space Or Losing The Trace File [ID 404833.1]

                How To Find Session Details and Trace Filename Using SELECTs [ID 377828.1]

                Init.ora Parameter "SQL_TRACE" Reference Note [ID 30820.1]

                Best Regards

                mseberg

                Edited by: mseberg on Aug 25, 2012 10:46 AM
                • 5. Re: Unable to generate trace in Oracle11gR2
                  sb92075
                  How do I ask a question on the forums?
                  SQL and PL/SQL FAQ
                  • 6. Re: Unable to generate trace in Oracle11gR2
                    Srini Chavali-Oracle
                    Moving this thread to the EBS forums

                    Pl post details of database, OS and EBS versions.

                    What output does this query return ?

                    HTH
                    Srini
                    • 7. Re: Unable to generate trace in Oracle11gR2
                      Hussein Sawwan-Oracle
                      where req.request_id = &request
                      Are you passing the correct request_id to the query? If not, please do so and you should get an output of tracing was enabled as per the doc.

                      Thanks,
                      Hussein