Oracle Database Discussions

I think there is no such command to trace a session.when ever a user gets connected to DB a session with sid,serial# gets associated with the user.

select sid,serial# from v$session where username='USER_NAME';

after that use

execute dbms_system.set_sql_trace_in_session(sid,serial#,true);

execute dbms_monitor.session_trace_enable(sid,serial#,true);

Hi,

Enabling the trace to the user level

User level - its self reflects to Sessionw which it gets connected to ORACLE in order to Access the data/information.
Check with EXECUTE dbms_support.start_trace_in_session (9,29); else enable the trace @DB level for particular time and disable with out restart.

HTH

- Pavan Kumar N
- ORACLE OCP - 9i/10g
https://www.oracleinternals.blogspot.com

I would not recommend to use DBMS_SUPPORT package since its not documented and is only meant for support. If you are on 10g and above, you can use DBMS_MONITOR package for the same.

HTH
Aman....

Hi Aman,

Agree on that.. Sir... !! :-)

- Pavan Kumar N

Hi..Gurus,

thanks for the updates. I have used all these options earlier ,which were posted by you all, but sill i am not able to get the trace for spawned(child ) processes automatically. Only options is that i need to monitor closely v$session, and i need to enable the trace for each new spawned process, but it is very tedious job reason being my program takes approximately 5 hours to complete the entire job,


regards
DBA.

then better trace the entire instance/database

The procedure actually creates new sessions ? How and why ? How does it supply the SQLs to be executed by the child sessions ?

You could modify the code so that the SQL code it passes to the child session includes the DBMS_SESSION.START_TRACE call !



OR write a database LOGON trigger that enables tracing, based on the username.


Hemant K Chitale