Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unanswered: Trace File not getting generated

    I am monitoring session which is an 'INSERT INTO... ' Job.
    I wish to set tracing on it but I do not see trace file generated with the following statement issued.
    SQL> exec dbms_system.set_sql_trace_in_session(78,178,TRUE)

    PL/SQL procedure successfully completed.

    The session is active .
    Pls suggest

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm, I'm not sure it will help ...

    I used trace utility on Ora7 and had to issue
    SQL> ALTER SESSION SET SQL_TRACE = TRUE;
    command first.

    Then I executed the query and, although trace file was visible, it was unusable until I left SQL*Plus session. The trace file was then closed and available for applying TKPROF against it.

    I also believe you checked trace file destionation folder, didn't you?

    Also, I have to admit I never used "dbms_system.set_sql_trace_in_session" so - sorry if I just bubbled nonsense.

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    I wish to set tracing ON for the job which has already started and not a query. The statement alster session... can be executed from the same session where your query to be traced is going to run .
    I have checked the destination where trace file is expected

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    set_sql_trace_in_session will write a trace file to to UDUMP destination.
    You will then need to run tkprof to put it in a readable form

    ie,
    tkprof ...\udump\oraxxx.trc newfilename.txt explain=username/password@sid

    HTH
    Gregg

  5. #5
    Join Date
    Mar 2004
    Posts
    10
    If you are tracing a query that runs in the context of a Job( I presume you mean DBMS_JOB), then the sql trace is generated by the executing job_queue_process and not the shadow process of the Sqlplus session.
    In this case it will be created in background_dump_dest and NOT in user_dump_dest. The process name will start with "j....".

    Cheers.
    Sudip Datta
    Server Technologies
    Oracle Corporation

  6. #6
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    I had checked both the destinations. There was no file created. I was verifying with the timestamp of the then present files in UDUMP and BDUMP.
    The job was not through dbms_jobs.It was a simple procedure to INSERT into a table which seemed to be in a HUNG state.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •