SQL Trace file name
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SQL Trace file name

  1. #1
    Join Date
    Feb 2006
    Posts
    162

    SQL Trace file name

    Hi,

    My Oracle version is 10gR2 and server is HP Unix. I wants to enable sql trace file for some oracle sessions,to find the trace file generated in udump, We need to have OS Process Id of that session. But in HP Unix, v$session.process & v$process.spid is not showing the exact os process id. What I've to do to find out the exact trace file generated for that session in HP Unix?

    Thanks...

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Talking Are you sure?

    Are you sure? V$SESSION works for me:
    Code:
    SQL> host uname -a
    HP-UX hpux5 B.11.11 U 9000/800 HP-UX
    
    SQL> host ps
       PID TTY       TIME COMMAND
     27447 pts/0     0:01 sqlplus
      1136 pts/0     0:00 ps
      3647 pts/0     0:00 ksh
    
    SQL> select sid, username, osuser, process, module
      2    from v$session
      3   where osuser='oracle'
      4     and module like 'sqlplus%'
      5  /
    
            SID USERNAME      OSUSER        PROCESS      MODULE
    ----------- ------------- ------------- ------------ ------------------------------
             22 SYS           oracle        27447        sqlplus@hpux5 (TNS V1-V3)
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    It should work.
    Enable trace and execute this query.

    select a.sid,a.serial#,a.osuser,a.machine,a.program,b.sql_text
    from v$session a,v$sqlarea b,v$process c
    where a.sql_hash_value=b.hash_value
    and a.serial# !=1
    and a.status='ACTIVE'
    and b.users_executing=1
    and a.paddr=c.addr
    AND A.SID NOT IN (select sid
    from v$session
    where audsid = userenv('sessionid'))
    and c.spid=&spid
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    You should see process ID in V$session defintely it should be there.

    But just for quick solution for your trace file name issue you can use below.

    alter session set tracefile_identifier='name_you_like';

    your tracefile should get generated as something like

    sid_ora_process_id_name_you_like.trc
    Rgds
    Parag

  5. #5
    Join Date
    Nov 2008
    Posts
    11
    Quote Originally Posted by malru
    Hi,

    My Oracle version is 10gR2 and server is HP Unix. I wants to enable sql trace file for some oracle sessions,to find the trace file generated in udump, We need to have OS Process Id of that session. But in HP Unix, v$session.process & v$process.spid is not showing the exact os process id. What I've to do to find out the exact trace file generated for that session in HP Unix?

    Thanks...
    Hi, I have no good idea, you can saw file's create time.This easy.

  6. #6
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Hi, I have no good idea, you can saw file's create time.This easy.
    If you do not know eaxctly, why do you reply for shake of it?
    http://www.perf-engg.com
    A performance engineering forum

  7. #7
    Join Date
    Nov 2008
    Posts
    11
    Quote Originally Posted by malay_biswal
    If you do not know eaxctly, why do you reply for shake of it?
    I have not good English. "no good idea " meaning "This no best idea, but it is easy ".
    note:udump only storage user trc file.
    1 you doing trc sql.
    2 you into udump's folder, you saw is create trc file, the trc file is create time is last.

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Great Wall
    I have not good English. "no good idea " meaning "This no best idea, but it is easy ".
    note:udump only storage user trc file.
    1 you doing trc sql.
    2 you into udump's folder, you saw is create trc file, the trc file is create time is last.
    This might be a good answer but for a different question.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width