Generate an SQL replay file using TKPROF

Handy hint. You can use TKPROF's "record" option to extract a 'replay' script from a trace.


SQL> exec dbms_monitor.session_trace_enable(binds => true);


PL/SQL procedure successfully completed.


SQL> create table trace_test (id number, val varchar2(20));


Table created.


SQL> insert into trace_test values (1,'abcef');


1 row created.


SQL> insert into trace_test values (2,'xyz');


1 row created.


SQL> disconn
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Then switch to the udump directory and find the trace file you created. 
In my case it was xe_ora_3972.trc

tkprof record=rec.txt trace= xe_ora_3972.trc output= r2.txt


TKPROF: Release 10.2.0.1.0 - Production on Sat Apr 2 10:02:28 2011


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Then you can rerun the output from your record.


H:\oraclexe\app\oracle\admin\XE\udump>more rec.txt
BEGIN dbms_monitor.session_trace_enable; END;
/
BEGIN dbms_monitor.session_trace_enable(binds => true); END;
/
create table trace_test (id number, val varchar2(20)) ;
insert into trace_test values (1,'abcef') ;
insert into trace_test values (2,'xyz') ;