Re: SQL TRACING FOR A PARTICULAR SESSION.

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:40444afd$1$22393$ed9e5944_at_reading.news.pipex.net...
| If you wish to do this then I think you will have to roll your own.
|
| I have played with writing a resource profiler using utl_file and trace
| files. I suspect that for me it would probably take a good 6 months of
work
| to get right. (this probably means it is a 3-6 week job for a competent
| coder).
|
| --
| Niall Litchfield
| Oracle DBA
| Audit Commission UK
| "Jaffery" <maulana1_at_hotmail.com> wrote in message
| news:3ff5ba5f.0403012114.2d142dbd_at_posting.google.com...
| > Hi,
| >
| > But you did not mention that how should the text from these sql text
| > files store into my table.
| > These sql trace files create when user logon.
| >
| > I know TKPROF is the utility via which we can read these sql trace
| > files.
| > But how should we read these text files dynamically(run time) and then
| > stores the text of these sql trace files into an Oracle
| > table(id,username, text) using logon/logof triggers etc.
| >
| > Any useful suggestion will be appreciated.
| >
| > Regards
| >
| > Jaffery
|

....

jumping in a little late, sorry if i missed anything

if you are looking to set trace a session then identify and preserve the log files, see an example of a function that sets sql trace and returns the tracefile name at http://www.enquery.com/presentations.html (also search for 'TRACEFILE NAME' at http://asktom.oracle.com)

this (or similar code) could be called in the logon trigger, perhaps referencing a configuration table that indicates which sessions (users) should be traced, or it could be called on demand. the trace file name could be saved in a configuration table for later loading with utl_file (note that you'll want to 'delete' the file after loading it, since oracle reuses trace files -- with utl_file, there is no delete, you'll need to reopen the file for writing to clear its contents -- of course you will also need to add your UDUMP directory to the UTL_FILE_DIR list in init.ora)

hope this is helpful -- repost or contact me if you want to flesh out the design a little more

;-{ mcs Received on Tue Mar 02 2004 - 06:48:51 CST