[20160704]从跟踪文件抽取sql语句.txt-CSDN博客

[20160704]从跟踪文件抽取sql语句.txt

--以前写过两篇,链接如下:
http://blog.itpub.net/267265/viewspace-775398/
http://blog.itpub.net/267265/viewspace-748041/

1个利用tkprof的recodr参数,可以记录跟踪整个sql语句序列,缺点就是丢失一些递归的sql语句.另外一个利用awk脚本抽取sql语句.
今天再写一个简单一点的脚本:

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> alter system flush shared_pool;
System altered.

2.测试脚本:
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';

3.使用tkprof:
$ tkprof /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_58768.trc record=a.sql
output = aaa
TKPROF: Release 11.2.0.4.0 - Development on Mon Jul 4 08:39:19 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

$ cat a.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' ;

4.使用http://blog.itpub.net/267265/viewspace-775398/的脚本:
--仅仅截取其中一部分:

$ trimsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_58768.trc
0001 0 #140358439135744>>>> 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
0002 1 #140358440042872>>>> select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
0003 1 #140358439558928>>>> select node,owner,name from syn$ where obj#=:1
0004 1 #140358438327056>>>> select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
0005 1 #140358438327056>>>> select order#,columns,types from access$ where d_obj#=:1
0006 1 #140358439513520>>>> select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj#=:1
0007 1 #140358438327056>>>> select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
0008 1 #140358438327056>>>> select order#,columns,types from access$ where d_obj#=:1
0009 1 #140358439504200>>>> select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ where obj#=:1 order by intcol#
0010 1 #140358438327056>>>> select text from view$ where rowid=:1
0011 1 #140358438327056>>>> select timestamp, flags from fixed_obj$ where obj#=:1
0019 1 #140358438327056>>>> select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH from GV$PARAMETER where inst_id = USERENV('Instance')
0020 1 #140358439538544>>>> select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee#
0021 1 #140358439533248>>>> select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
0022 1 #140358438190560>>>> select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by intcol# desc
0023 1 #140358439604024>>>> select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltype$ where obj#=:1 order by intcol# asc
0024 1 #140358439598312>>>> select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc
0025 1 #140358439130312>>>> select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l.pctversion$, l.flags, l.property, l.retention, l.freepools from lob$ l where l.obj# = :1 order by l.intcol# asc
0026 1 #140358439591048>>>> select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by intcol# asc
0027 1 #140358439585032>>>> select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intcol# asc
0028 1 #140358439579016>>>> select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtype$ where obj# = :1 order by intcol# asc
0029 0 #140358439130312>>>> drop table t purge
....
0287 0 #140358438518416>>>> commit
0288 0 #140358438246176>>>> insert into t values(2)
0289 0 #140358438246176>>>> rollback
0290 0 #140358438448232>>>> BEGIN :va := 1; END;
0291 0 #140358438448232>>>> select * from t where a=:va
0292 1 #140358438434848>>>> SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."A"=:B1 THEN 1 ELSE 0 END AS C2 FROM "SCOTT"."T" "T") SAMPLESUB
0293 0 #140358438448232>>>> alter session set events '10046 trace name context off'

--第2字段的0表示执行的sql语句,1表示递归执行的sql语句.

5.另外一个简单的脚本:

$ cat extractsql.sh
#! /bin/bash
$ awk '/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT'

$ extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_58768.trc | grep ^drop -A20
drop table t purge
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj#=:1
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
select decode(u.type#, 2, u.ext_username, u.name), o.name, trigger$.sys_evts, trigger$.type# from   obj$ o, user$ u, trigger$ where o.type# = 12 and (bitand(trigger$.property, 8) = 8) and   (bitand(trigger$.property, 16) != 16) and   trigger$.obj# = o.obj# and o.owner# = u.user# order by o.obj#
select value$ from sys.props$ where name = :1
SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME = 'OGG_TRIGGER_OPTIMIZATION'
select audit$,options from procedure$ where obj#=:1
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
select order#,columns,types from access$ where d_obj#=:1
select actionsize from trigger$ where obj# = :1
select action# from trigger$ where obj# = :1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid,actionlineno,trignameline,trignamecol, trignamecolofs,actioncolno from trigger$ where obj# =:1
select tc.type#,tc.intcol#,tc.position#,c.type#, c.length,c.scale,c.precision#,c.charsetid,c.charsetform, decode(bitand(c.property,8388608),8388608, c.spare3, 0) from triggercol$ tc,col$ c, trigger$ tr where tc.obj#=:1 and tc.intcol#=c.intcol# and tr.obj# = tc.obj# and (bitand(tr.property,32) != 32 or bitand(tc.type#,20) = 20) and (   c.obj#=:2 and (bitand(tc.type#, 1024) = :3 or tc.type# = 0)      or c.obj#=:4 and bitand(tc.type#, 1024) = :5      or c.obj#=:6 and tc.type# = 0 and tc.position# = 0) union select type#,intcol#,position#,69,0,0,0,0,0,0 from triggercol$ where obj#=:7 and intcol#=1001 union select tc.type#,tc.intcol#,tc.position#,121,0,0,0,0,0,0 from triggercol$ tc,trigger$ tr where tr.obj# = tc.obj# and bitand(tr.property,32) = 32 and tc.obj# = :8 and bitand(tc.type#,20) != 20
select case when (bitand(u.spare1, 16) = 0) then         0        when (u.type# = 2) then         (u.spare2)        else         1        end from obj$ o, user$ u where o.obj# = :1 and o.owner# = u.user#
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,ext_username,spare2 from user$ where name=:1
select audit$,options from procedure$ where obj#=:1

$ extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_58768.trc | tail -20
select sdo_geor_def.listAllGeoRasterFieldsStr(:1,:2) from dual
SELECT column_name,data_type_owner,data_type FROM dba_tab_columns where owner=:o and table_name=:t
select node,owner,name from syn$ where obj#=:1
select decode(u.type#, 2, u.ext_username, u.name), o.name, t.update$, t.insert$, t.delete$, t.enabled, decode(bitand(t.property, 8192),8192, 1, 0), decode(bitand(t.property, 65536), 65536, 1, 0), decode(bitand(t.property, 131072), 131072, 1, 0), (select o.name from obj$ o where o.obj# = u.spare2 and o.type# =57) from sys.obj$ o, sys.user$ u, sys.trigger$ t, sys.obj$ bo where t.baseobject=bo.obj# and bo.name = :1 and bo.spare3 = :2 and bo.namespace = 1 and t.obj#=o.obj# and o.owner#=u.user# and o.type# = 12 and bitand(property,16)=0 and bitand(property,8)=0 order by o.obj#
insert into t values(1)
select pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg, maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg, maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg, cmpflag_stg, cmplvl_stg from deferred_stg$ where obj# =:1
delete from deferred_stg$ where obj# = :1
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapranges) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0,NULL,:17),:18,:19)
delete from superobj$ where subobj# = :1
delete from tab_stats$ where obj#=:1
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35 where obj#=:1
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
commit
insert into t values(2)
rollback
BEGIN :va := 1; END;
select * from t where a=:va
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."A"=:B1 THEN 1 ELSE 0 END AS C2 FROM "SCOTT"."T" "T") SAMPLESUB
alter session set events '10046 trace name context off'