How to capture activities for a specific session

[lwangf]

Hi all,

I want to capture the activities for a particular session, not only the current activitiy but also the history. Please help. Thanks

[lwangf]

The database is Oracle7.3.4 on Solaris2.6 box.

[sreddy]

http://otn.oracle.com/docs/products/...4_str.htm#8723

[lwangf]

Thanks sreddy. Is there any other way except using TKPROF? Is there any v$ views I can use to capture all the sql statements for that session?

[eimutis]

While there are a number of views (e.g. V$SESSION, V$SESSTAT, etc.) that hold stats for a SID, sreddy is right; the best is TKPROF. Not that hard once you've done it a few dozen times. Also, you need to know ahead of time that you will want to trace a particular session.

Also, the new OEM 9i has some great GUI tools to let you track just about everything a particular sesssion is doing, including stuff it's waiting for, its I/O, the SQL history, explain plan for any SQL, etc.

Pretty powerfull stuff. I have it running routinely on my production DBs.

[mrvajrala]

You can find SQL_ADDRESS and SQL_HASH_VALUE in v$session and map these column values for a perticular session to the columns ADDRESS, HASH_VALUE in v$SQLAREA / v$SQL_TEXT_WITH_NEWLINES..

This will get you the query run by a perticular session...

[sreddy]

Originally posted by lwangf
Hi all,

I want to capture the activities for a particular session, not only the current activitiy but also the history. Please help. Thanks

USE TKPROF/V$ views (V$SQL, V$SQL_TEXT, V$OPEN_CURSORS,V$SQL_AREA to get the SQL satements for current activity and Use logminer for history. I think its staight forward and pretty easier to use TKPROF than building your own customized queries/reports from these views specific to a session.

[lwangf]

Thanks for all your help. I will use tkprof to gether the info.