Home » SQL & PL/SQL » SQL & PL/SQL » extract sql statements from trace file in windows environment ( windows 07)
extract sql statements from trace file in windows environment [message #627588] Wed, 12 November 2014 22:31 Go to next message
raja.i
Messages: 11
Registered: November 2014
Junior Member
Hi,
i want to extract only sql statements(SELECT, INSERT, UPDATE, DELETE) from trace file(.trc) and obtaining file should be in the same .trc format. Please help me to do, as it is very urgent.
Re: extract sql statements from trace file in windows environment [message #627589 is a reply to message #627588] Wed, 12 November 2014 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

> as it is very urgent.

Why is it urgent for me to solve this problem for you?

what exactly produces the trace file & what is its internal format?
Re: extract sql statements from trace file in windows environment [message #627590 is a reply to message #627589] Wed, 12 November 2014 23:09 Go to previous messageGo to next message
raja.i
Messages: 11
Registered: November 2014
Junior Member
I need to extract the complete sql history from sql trace files to "debug" a client application.
I know I can read the raw trc file and rebuild the sql history looking for the PARSING / EXEC / FETCH entries.
However, this is a very long and boring manual task: do you know if there is some free tool to automate this task?
thanks
Re: extract sql statements from trace file in windows environment [message #627591 is a reply to message #627590] Wed, 12 November 2014 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
tkprof can be used produce SQL statements from trace file.

[oracle@localhost ~]$ tkprof 
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

[oracle@localhost ~]$ 


Re: extract sql statements from trace file in windows environment [message #627592 is a reply to message #627591] Wed, 12 November 2014 23:20 Go to previous messageGo to next message
raja.i
Messages: 11
Registered: November 2014
Junior Member
thanks, but it will return execution plan for each and every sql statements. because of that, it will take more time to cut and paste each and every sql. as i want only sql(select, insert, update, dleete) statements please give some other idea dear. thanks much
Re: extract sql statements from trace file in windows environment [message #627593 is a reply to message #627592] Wed, 12 November 2014 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
tkprof produces EXPLAIN PLAN only if you direct it to do so.

If your require whole, multi-line, & complete SQL statements, I think you will need to write your own custom extractor code.


What problem are you really trying to solve?
How will you, I or anyone know when correct solution has been posted?
Re: extract sql statements from trace file in windows environment [message #627594 is a reply to message #627593] Wed, 12 November 2014 23:38 Go to previous messageGo to next message
raja.i
Messages: 11
Registered: November 2014
Junior Member
what is custom extractor code ?? pl reply
Re: extract sql statements from trace file in windows environment [message #627595 is a reply to message #627594] Wed, 12 November 2014 23:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
raja.i wrote on Wed, 12 November 2014 21:38
what is custom extractor code ?? pl reply


I could write code in PERL or PL/SQL to parse file from tkprof to produce only SQL statements.
In what programming languages are you proficient?
Re: extract sql statements from trace file in windows environment [message #627596 is a reply to message #627595] Thu, 13 November 2014 00:02 Go to previous messageGo to next message
raja.i
Messages: 11
Registered: November 2014
Junior Member
i am good in pl/sql. how to write custom extractor code in pl/sql??
Re: extract sql statements from trace file in windows environment [message #627598 is a reply to message #627596] Thu, 13 November 2014 00:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
raja.i wrote on Wed, 12 November 2014 22:02
i am good in pl/sql. how to write custom extractor code in pl/sql??

remove unwanted text & keep desired text
Re: extract sql statements from trace file in windows environment [message #627600 is a reply to message #627598] Thu, 13 November 2014 00:12 Go to previous messageGo to next message
raja.i
Messages: 11
Registered: November 2014
Junior Member
hi thanks for your replies,
but if i removind unwanted and keeping desired then, it will take more time as it has more than 40k lines. so nly i want some tool to extract sql from .trc file.waiting to hear frm u
Re: extract sql statements from trace file in windows environment [message #627602 is a reply to message #627600] Thu, 13 November 2014 00:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
raja.i wrote on Wed, 12 November 2014 22:12
hi thanks for your replies,
but if i removind unwanted and keeping desired then, it will take more time as it has more than 40k lines. so nly i want some tool to extract sql from .trc file.waiting to hear frm u


if no such tool exists, what are your options?
Re: extract sql statements from trace file in windows environment [message #627603 is a reply to message #627602] Thu, 13 November 2014 00:23 Go to previous messageGo to next message
raja.i
Messages: 11
Registered: November 2014
Junior Member
I have to do manually yaar. thanks much.
Re: extract sql statements from trace file in windows environment [message #627606 is a reply to message #627590] Thu, 13 November 2014 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
do you know if there is some free tool to automate this task?


Write it and share it here then. Wink

Re: extract sql statements from trace file in windows environment [message #627611 is a reply to message #627591] Thu, 13 November 2014 01:44 Go to previous message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Use tkprof. It has a record= option.
Previous Topic: finding the gaps in between the sequences
Next Topic: sending mail using java in oracle PL/SQL 11g
Goto Forum:
  


Current Time: Thu Apr 25 09:45:25 CDT 2024