[20121101]tkprof抽取sql语句.txt

有时候跟踪分析sql语句,并不是要看里面的递归的执行,或者性能问题.而是要收集跟踪命令的执行序列.

查看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.

...

有一个参数record=filename,可以记录跟踪整个sql语句序列,自己做一个简单测试看看:

alter session set events '10046 trace name context forever, level 12';

show parameter cursor;

drop table t purge ;

create table t (a number);

insert into t values(1);

commit ;

insert into t values(2);

rollback;

variable va number;

exec :va := 1

select * from t where a=:va;

desc t

alter session set events '10046 trace name context off';

确定跟踪文件:

$ tkprof /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_30100.trc record=aaa.sql

SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',        6,'big integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM ;

drop table t purge  ;

create table t (a number) ;

insert into t values(1) ;

commit  ;

insert into t values(2) ;

rollback ;

BEGIN :va := 1; END;

/

select * from t where a=:va ;

alter session set events '10046 trace name context off' ;

--除desc t外,其他都记录下来.

--show parameter cursor;变成了

SELECT NAME NAME_COL_PLUS_SHOW_PARAM, DECODE(TYPE, 1,'boolean', 2,'string', 3,'integer', 4,'file', 5,'number', 6,'big integer', 'unknown') TYPE, DISPLAY_VALUE

       VALUE_COL_PLUS_SHOW_PARAM

  FROM V$PARAMETER

 WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ)

 ORDER BY NAME_COL_PLUS_SHOW_PARAM, ROWNUM ;