SQL Trace file name

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

[LKBrwn_DBA]

Are you sure? V$SESSION works for me:

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)

[malay_biswal]

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

[paragp1981]

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

[Great Wall]

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.

[malay_biswal]

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?

[Great Wall]

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.

[PAVB]

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.