Oracle DBA – A lifelong learning experience

Getting sql statements out of a trace file

Posted by John Hallas on March 15, 2015

The focus on this post started off in one direction and ended up in another. Originally I had been running a drop user script which had hung and even when I killed the process I could not drop the users as it gave a “ORA-01940: cannot drop a user that is currently connected” – despite the users having left the company months ago and there being no chance of them actually having connected sessions. My suspicions were that the drop user command actually took a lock on the users or connected as them whilst dropping them.  I was also intrigued by the length of time it took to drop users who had no objects.  Therefore I created a user, dropped it and traced the session to see what was happening. I was amazed by the size of the output file and that is where the direction changed. I wanted to find an easy way to get all the lines of code out of  trace file so that I could review them quickly.

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 14 06:55:08 2015
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

create user test identified by test;
User created.
set timing on
ALTER SESSION SET sql_trace=TRUE;
Session altered.
Elapsed: 00:00:00.00
drop user test;
User dropped.
Elapsed: 00:00:00.26
ALTER SESSION SET sql_trace=FALSE;
Session altered.
Elapsed: 00:00:00.00

I now had a trace file and I used the insert=filename parameter of tkprof to produce a script containing all the sql_statements in the trace file, in the same order.

tkprof xe_ora_5796.trc xe_ora_5796.tkp insert=xe_ora_5796.sql

Edit the sql file that is produced (xe_ora_57967.sql in my example) , changing the field SQL_STATEMENT from LONG to a varchar2(4000)

REM Edit and/or remove the following CREATE TABLE

REM statement as your needs dictate.

CREATE TABLE tkprof_table
(
date_of_insert                       DATE
,cursor_num                           NUMBER
,depth                               NUMBER
,user_id                            NUMBER
,parse_cnt                           NUMBER
,parse_cpu                           NUMBER
,parse_elap                           NUMBER
,parse_disk                           NUMBER
,parse_query                         NUMBER
,parse_current                       NUMBER
,parse_miss                           NUMBER
,exe_count                           NUMBER
,exe_cpu                             NUMBER
,exe_elap                             NUMBER
,exe_disk                             NUMBER
,exe_query                           NUMBER
,exe_current                         NUMBER
,exe_miss                             NUMBER
,exe_rows                             NUMBER
,fetch_count                         NUMBER
,fetch_cpu                          NUMBER
,fetch_elap                           NUMBER
,fetch_disk                           NUMBER
,fetch_query                         NUMBER
,fetch_current                       NUMBER
,fetch_rows                           NUMBER
,ticks                               NUMBER
,sql_statement                       varchar2(4000)
);

Now I ran a simple select from the created table tkprof_table and voila! – all the sql statements nicely formatted for viewing.
At this point I had decided that one of the packages early on must take a lock on the user – probably the dbms_cdc_utility.drop_user procedure,  although seems to be more focussed on dropping change tables during a drop user command.

  select substr(sql_statement,1,130) from tkprof_table;

SUBSTR(SQL_STATEMENT,1,130)
----------------------------------------------------------------------------------------------------------------------------------------------------------------
alter session set sql_trace=true
BEGIN DBMS_SESSION.set_sql_trace(sql_trace => TRUE); END;
drop user test
BEGIN
BEGIN
IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_CONTENTS)) TH

select text from view$ where rowid=:1
select obj# from RecycleBin$ where owner#=:1 and    to_number(bitand(flags, 4)) = 4
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, de
select obj#, type#, flags, related, bo, purgeobj, con#    from RecycleBin$ where owner#=:1
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and names
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.ini
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.di
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,s
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#,
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#
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,s
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
select null from obj$ where owner#=:1 and type#!=10 union all select null from link$ where owner#=:1 union all select null from st
select u.name, o2.name, o2.obj# from ind$ i, obj$ o1, obj$ o2, user$ u where o1.owner# = :1  and o1.type# = 2 and i.type# = 9 and
select u.name, o.name, o.obj# from obj$ o, user$ u, ind$ i where o.owner#=:1 and o.owner#=u.user# and o.obj#=i.obj# and i.type#=9
begin sys.dbms_cdc_utility.drop_user(:1); end;
begin sys.dbms_parallel_execute_internal.drop_all_tasks(:1); end;
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, ob
select order#,columns,types from access$ where d_obj#=:1
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piec
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piec
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by p
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piec
SELECT USER_ID FROM DBA_USERS WHERE USERNAME = :B1
select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj#=:1
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by i
select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltype$ where obj#=:1 order by intcol# asc
select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc
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.f
select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intcol# asc
select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,  elemnum from opqtype$ where obj# = :1 order by intcol# asc
select name from obj$ where owner# = :1 and type# = 82
select vname from sys.snap$ where sowner = :1 and instsite = 0  and parent_vname IS NULL
select name from sys.transformations$ where owner = :1
select queue_name from "_DBA_STREAMS_QUEUES" where queue_owner=:1
delete from system.aq$_internet_agent_privs WHERE db_username = NLS_UPPER(:1)
select decode(u.type#, 2, u.ext_username, u.name), o.name,        t.update$, t.insert$, t.delete$, t.enabled,        decode(bitand
select name from system.aq$_queue_tables where schema = :1
select o.name from rule_set$ rs, obj$ o, user$ u where u.name = :1 and  o.owner# = u.user# and o.obj# = rs.obj#
select o.name from rule$ r, obj$ o, user$ u where u.name = :1 and  o.owner# = u.user# and o.obj# = r.obj#
SELECT X.OBJNUM FROM  (select a.obj# OBJNUM, b.owner# OWNNUM   from sys.scheduler$_job a, sys.obj$ b   where a.obj# = b.obj#   uni
select bo#, intcol# from icoldep$ where obj#=:1
select a.obj# from sys.scheduler$_program a, sys.obj$ b  where a.obj# = b.obj# and b.owner# = :1
select a.obj# from sys.scheduler$_schedule a, sys.obj$ b  where a.obj# = b.obj# and b.owner# = :1
select a.obj# from sys.scheduler$_chain a, sys.obj$ b  where a.obj# = b.obj# and b.owner# = :1
select a.obj# from sys.scheduler$_destinations a, sys.obj$ b  where a.obj# = b.obj# and b.owner# = :1
select a.obj# from sys.scheduler$_file_watcher a, sys.obj$ b  where a.obj# = b.obj# and b.owner# = :1
select a.obj# from sys.scheduler$_credential a, sys.obj$ b  where a.obj# = b.obj# and b.owner# = :1
select a.obj# from sys.scheduler$_window_group a, sys.obj$ b  where a.obj# = b.obj# and b.owner# = :1
select a.obj# from sys.scheduler$_job a, sys.user$ u  where bitand(a.job_status, 1) = 1 and  u.user# = :1 and u.name = a.queue_own
DECLARE   sqlcur        NUMBER;   dummy         NUMBER;   id            NUMBER;   user_name     VARCHAR2(30) := :1;   sqltxt
SELECT USER# FROM SYS.USER$ U WHERE U.NAME = :B1
SELECT COMPARISON_ID FROM COMPARISON$ WHERE USER# = :B1
select max(obj#) from edition$
select o.name,o.type#,o.obj#,o.remoteowner,o.linkname,o.namespace, o.subname from obj$ o, tab$ t  where o.owner#=:1 and    (bitand
select name, type#, obj#, remoteowner, linkname, namespace, subname from obj$ o where (bitand(:2,2)=2) and o.owner#=:1 and type# =
select name, type#, obj#, remoteowner, linkname, namespace, subname from obj$ o where (bitand(:2,2)=2) and o.owner#=:1 and type# =
select name, type#, obj#, remoteowner, linkname, namespace, subname from obj$ o where (bitand(:2,2)=2) and o.owner#=:1 and (type#
select name, type#, obj#, remoteowner, linkname, namespace, subname from obj$ o where o.owner#=:1 and       ((bitand(:2,1)=1) and
select name, type#, obj#, remoteowner, linkname, namespace, subname from obj$ o where (bitand(:2,1)=1) and o.owner#=:1 and type#=1
select name,type#,obj#,remoteowner,linkname,namespace, subname from obj$ o where (bitand(:2,1)=1) and o.owner#=:1 and type# = 13
select name,type#,obj#,remoteowner,linkname,namespace, subname from obj$ o where (bitand(:2,1)=1) and o.owner#=:1 and type# = 14
select o.name, o.type#, o.obj#, o.remoteowner, o.linkname, o.namespace,         o.subname from obj$ o where o.owner# = :1   and (b
select name, type#, obj#, remoteowner, linkname, namespace, subname  from obj$ o  where (bitand(:2,1)=1) and o.owner#=:1 and type#
select p_obj# from edition$ where obj# = :1
select name from link$ where owner#=:1
select seq, owner, pack, proc from sys.duc$ where operation#=1  and seq > :1 or (seq = :1 and        (owner > :2 or (owner = :2 an
begin "CTXSYS"."CTX_ADM"."DROP_USER_OBJECTS"(:myuser); end;
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass)
select node,owner,name from syn$ where obj#=:1
select timestamp, flags from fixed_obj$ where obj#=:1
select inst_id,addr,ksqrsidt,ksqrsid1,ksqrsid2 from x$ksqrs where bitand(ksqrsflg,2)!=0
select  ADDR , TYPE , ID1 , ID2 from GV$RESOURCE where inst_id = USERENV('Instance')
SELECT ID1 FROM V$RESOURCE WHERE TYPE = 'RT' AND ID2 = 0 AND ROWNUM = 1
SELECT THS_ID FROM DR$THS WHERE THS_OWNER# IN (SELECT USER# FROM SYS.USER$ WHERE NAME = :B1 )
select metadata from kopm$  where name='DB_FDO'
SELECT SPL_ID FROM DR$STOPLIST WHERE SPL_OWNER# IN (SELECT USER# FROM SYS.USER$ WHERE NAME = :B1 )
SELECT SGP_ID FROM DR$SECTION_GROUP WHERE SGP_OWNER# IN (SELECT USER# FROM SYS.USER$ WHERE NAME = :B1 )
DELETE FROM DR$INDEX_SET_INDEX WHERE IXX_IXS_ID IN (SELECT IXS_ID FROM DR$INDEX_SET WHERE IXS_OWNER# IN (SELECT USER# FROM SYS.USE
DELETE FROM DR$INDEX_SET WHERE IXS_OWNER# IN (SELECT USER# FROM SYS.USER$ WHERE NAME = :B1 )
SELECT IDX_NAME, IDX_ID FROM DR$INDEX WHERE IDX_OWNER# IN (SELECT USER# FROM SYS.USER$ WHERE NAME = :B1 )
SELECT USERENV('SCHEMAID') FROM DUAL
begin "SYS"."DBMS_DEFER_IMPORT_INTERNAL"."DROP_PROPAGATOR_CASCADE"(:myuser); end;
begin "SYS"."DBMS_IJOB"."DROP_USER_JOBS"(:myuser); end;
select schema, package, flags from context$ where obj#=:1
SELECT USER# FROM SYS.USER$ WHERE NAME = :B1
DELETE FROM REGISTRY$SCHEMAS WHERE SCHEMA# = :B1
begin "SYS"."DBMS_REPCAT_RGT_UTL"."DROP_USER_TEMPLATES"(:myuser); end;
DELETE FROM SYSTEM.REPCAT$_TEMPLATE_SITES WHERE USER_NAME = :B1
begin "SYS"."DBMS_REPCAT_UTL"."DROP_USER_REPSCHEMA"(:myuser); end;
SELECT C.CHARSETID FROM SYS.COL$ C, OBJ$ O, USER$ U WHERE C.CHARSETFORM = :B1 AND U.NAME='SYSTEM' AND U.USER#=O.OWNER# AND O.NAME=
select value$ from sys.props$ where name = :1
SELECT TYPE, ONAME FROM REPCAT_GENERATED WHERE SNAME = :B1 FOR UPDATE
SELECT STATUS FROM SYSTEM.REPCAT$_REPOBJECT WHERE SNAME = :B3 AND ONAME = :B2 AND TYPE = :B1 FOR UPDATE
DELETE FROM SYSTEM.REPCAT$_REPCATLOG WHERE SNAME = :B1 AND (GNAME IS NOT NULL OR ONAME IS NOT NULL)
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nt
select owner#, status from obj$ o 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
select case when (bitand(u.spare1, 16) = 0) then         0        when (u.type# = 2) then         (u.spare2)        else         1
UPDATE DBMS_ALERT_INFO SET CHANGED = 'Y', MESSAGE = :B2 WHERE NAME = UPPER(:B1 )
SELECT SID FROM DBMS_ALERT_INFO WHERE NAME = UPPER(:B1 )
SELECT DISTINCT ONAME, TYPE FROM SYSTEM.REPCAT$_REPOBJECT WHERE SNAME = :B5 AND TYPE IN (:B4 , :B3 , :B2 , :B1 ) AND ONAME NOT IN
DELETE FROM SYSTEM.REPCAT$_REPOBJECT WHERE SNAME = :B1
DELETE FROM SYSTEM.REPCAT$_GROUPED_COLUMN WHERE SNAME = :B1
DELETE FROM SYSTEM.REPCAT$_COLUMN_GROUP WHERE SNAME = :B1
DELETE FROM SYSTEM.REPCAT$_PARAMETER_COLUMN WHERE SNAME = :B1
DELETE FROM SYSTEM.REPCAT$_RESOLUTION WHERE SNAME = :B1
DELETE FROM SYSTEM.REPCAT$_CONFLICT WHERE SNAME = :B1
DELETE FROM SYSTEM.REPCAT$_REPCAT WHERE GOWNER = :B1
SELECT USER_ID FROM SYS.DBA_USERS WHERE USERNAME = :B1
SELECT COUNT(*) FROM DBA_REPGROUP_PRIVILEGES RP WHERE RP.USERNAME = :B4 AND ((:B2 = :B3 AND RP.RECEIVER = 'Y') OR (:B2 = :B1 AND R
select 1 from dual where exists (select 1 from system.repcat$_repprop prop  where prop.type in (-1,2,9,-4) and prop.how in (1,3))
begin "SYS"."DBMS_SQLTUNE_INTERNAL"."I_DROP_USER_SQLSETS"(:myuser); end;
SELECT USER# FROM USER$ WHERE NAME = :B1
SELECT ID, NAME FROM DBA_SQLSET_DEFINITIONS WHERE OWNER = :B1
begin "SYS"."DBMS_STREAMS_ADM_UTL"."PROCESS_DROP_USER_CASCADE"(:myuser); end;
SELECT U.USER# FROM USER$ U WHERE U.NAME = :B1
SELECT CAPTURE_NAME, STATUS, DECODE(FLAGS, 512, 'YES', 'NO') IS_SYNC_CAP FROM SYS.STREAMS$_CAPTURE_PROCESS WHERE CAPTURE_USERID =
SELECT APPLY_NAME, STATUS FROM SYS.STREAMS$_APPLY_PROCESS WHERE APPLY_USERID = :B1
SELECT PRIVILEGE_TYPE, PRIVILEGE_LEVEL FROM SYS.GOLDENGATE$_PRIVILEGES WHERE USERNAME = :B1 FOR UPDATE
begin "SYS"."PRVT_ADVISOR"."DELETE_USER_TASKS"(:myuser); end;
SELECT TASK_ID FROM SYS.DBA_ADVISOR_TASKS WHERE OWNER = :B1
begin "SYS"."DBMS_ISNAPSHOT"."DROP_USER_SNAPSHOTS"(:myuser); end;
DELETE FROM SYS.SLOG$ WHERE MOWNER=:B1
DELETE FROM SYS.MLOG$ WHERE MOWNER=:B1
SELECT DISTINCT SITE.SITE_NAME FROM SYS.SNAP$ S, SYS.SNAP_SITE$ SITE WHERE S.SOWNER = :B1 AND S.INSTSITE = SITE.SITE_ID
DELETE FROM SYS.SNAP$ WHERE SOWNER=:B1
DELETE FROM SYS.REG_SNAP$ WHERE SOWNER = :B2 AND SNAPSITE = :B1
DELETE FROM SYS.SNAP_COLMAP$ WHERE SOWNER = :B1
DELETE FROM SYS.SNAP_REFOP$ WHERE SOWNER = :B1
DELETE FROM SYS.SNAP_REFTIME$ WHERE SOWNER = :B1
DELETE FROM SYS.MLOG_REFCOL$ WHERE MOWNER = :B1
DELETE FROM SYS.SNAP_OBJCOL$ WHERE SOWNER = :B1
begin "SYS"."DBMS_IREFRESH"."DROP_USER_GROUPS"(:myuser); end;
DELETE FROM RGCHILD$ WHERE OWNER = :B1
select ts#,file#,block#,cols,nvl(size$,-1),pctfree$,pctused$,initrans,maxtrans,hashkeys,func,extind,avgchn,nvl(degree,1),nvl(insta
DELETE FROM RGCHILD$ WHERE REFGROUP IN (SELECT REFGROUP FROM RGROUP$ WHERE OWNER = :B1 )
DELETE FROM RGROUP$ WHERE OWNER = :B1
select OBJ#, PNAME from sys.fga$ where POWNER#=:1
select default$ from col$ where rowid=:1
select indmethod# from ind$ where obj#=:1
select max(intcol#) from col$ where obj#=:1
select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize, d
SELECT count(*) FROM XDB.XDB$XDB_READY
SELECT count(rowid) FROM XDB.XDB$ROOT_INFO
select T.pathtabobj#,T.flags,T.rawsize,T.parameters.getClobVal(), T.pendtabobj#,T.snapshot from xdb.xdb$dxptab T where idxobj#=:1
select name,owner# from obj$ where obj#=:1
select count(distinct(groupname)) from xdb.xdb$xtab where idxobj#=:1
select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by intcol# asc
select obj# from oid$ where user#=:1 and oid$=:2
select obj#,implobj#,property, interface_version# from indtypes$ where obj#=:1
select obj#,oper#,bind#,property,filt_nam,filt_sch, filt_typ from indop$ where obj#=:1
select audit$,properties from type_misc$ where obj#=:1
select source from source$ where obj#=:1 order by line
select audit$ from library$ where obj#=:1
delete from xdb.xdb$xidx_param_t where userid = :1
select name from sys.obj$ where owner# = :1
delete from sysauth$ where grantee#=:1 or privilege#=:1
delete from proxy_info$ where client# = :1 or proxy# = :1
select grantor#,ta.obj#,o.type# from objauth$ ta, obj$ o where grantee#=:1 and ta.obj#=o.obj# group by grantor#,ta.obj#,o.type#
select distinct subscription_name, namespace from reg$ where user# = :1
select distinct location_name from reg$ minus select distinct location_name  from reg$ where user# != :1
select blocks,maxblocks,grantor#,priv1,priv2,priv3 from tsq$ where ts#=:1 and user#=:2
select name,online$,contents$,undofile#,undoblock#,blocksize,dflmaxext,dflinit,dflincr,dflextpct,dflminext, dflminlen, owner#,scnw
select  decode(u.type#, 2, u.ext_username, u.name), o.name, trigger$.sys_evts, trigger$.type#  from obj$ o, user$ u, trigger$  whe
delete from user_history$ where user# = :1
delete sys.streams$_prepare_ddl p  where ((p.global_flag = 1 and :1 is null) or         (p.global_flag = 0 and p.usrid = :2))
BEGIN
aw_drop_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
END;

declare
stmt varchar2(200);
cnt number;
BEGIN
if sys.dbms_standard.dictionary_obj_type = 'USER' THEN
stmt := 'DE

DELETE FROM SDO_GEOM_METADATA_TABLE  WHERE SDO_OWNER = :owner
DELETE FROM SDO_MAPS_TABLE  WHERE SDO_OWNER = :owner
DELETE FROM SDO_CACHED_MAPS_TABLE  WHERE SDO_OWNER = :owner
DELETE FROM SDO_STYLES_TABLE  WHERE SDO_OWNER = :owner
DELETE FROM SDO_THEMES_TABLE  WHERE SDO_OWNER = :owner
DELETE FROM SDO_LRS_METADATA_TABLE  WHERE SDO_OWNER = :owner
DELETE FROM SDO_TOPO_METADATA_TABLE  WHERE SDO_OWNER = :owner
DELETE FROM SDO_ANNOTATION_TEXT_METADATA  WHERE F_TABLE_SCHEMA = :owner
delete from user$ where name=:1
select spare2  from user$ where type# = 2 and ext_username = :1
select user# from user$ where type# = 3 and spare2 = :1
BEGIN DBMS_SESSION.set_sql_trace(sql_trace => FALSE); END;
alter session set sql_trace=false
ALTER SESSION SET sql_trace=TRUE
SELECT /*+ ALL_ROWS */ COUNT(*) FROM DBA_POLICIES V WHERE V.OBJECT_OWNER = :B3 AND V.OBJECT_NAME = :B2 AND (V.POLICY_NAME LIKE '%x
DELETE FROM DBMS_PARALLEL_EXECUTE_TASK$ WHERE TASK_OWNER# = :B1
delete from sys.streams$_propagation_process where source_queue_schema = :1
begin dbms_rule_adm.drop_evaluation_context(:1, true); end;
SELECT PRE_ID FROM DR$PREFERENCE WHERE PRE_OWNER# IN (SELECT USER# FROM SYS.USER$ WHERE NAME = :B1 )
DELETE FROM DR$SQE WHERE SQE_OWNER# IN (SELECT USER# FROM SYS.USER$ WHERE NAME = :B1 )
SELECT COUNT(*) FROM DEFPROPAGATOR WHERE USERNAME = :B1
DELETE FROM SYS.JOB$ WHERE :B1 = POWNER OR :B1 = COWNER OR :B1 = LOWNER
begin "SYS"."DBMS_REGISTRY_SYS"."DROP_USER"(:myuser); end;
DELETE FROM REGISTRY$ WHERE INVOKER# = :B1 OR SCHEMA# = :B1
SELECT CHARSETID FROM SYS.COL$ C, OBJ$ O, USER$ U WHERE CHARSETFORM = :B1 AND U.NAME='SYSTEM' AND U.USER#=O.OWNER# AND O.NAME='REP
DELETE FROM SYSTEM.REPCAT$_AUDIT_COLUMN WHERE SNAME = :B1
COMMIT
update xdb.xdb$acl set object_value = deletexml(object_value, '/acl/ace[principal="' || :1 || '"]', 'xmlns="http://xmlns.oracle.co
select s.xmldata.schema_url     from xdb.xdb$schema s, xdb.xdb$resource r     where r.xmldata.ownerid = :1     and r.xmldata.xmlre
delete from proxy_role_info$ where client# = :1 or proxy# = :1
delete from defrole$ where user#=:1

210 rows selected.

So a simple drop user command where a user has no privileges or objects generates 210 distinct sql statements. No wonder it takes a while to run sometimes.

I was quite pleased with this but feel it worth sharing a couple of other ideas I had looked at before using the insert option of tkprof.  I couldn’t find a utility that extracts only the SQL from a trace file, other than TOAD’s trace file analyser, so Norman Dunbar, a colleague,  created an awk script. He also told me about a useful utility called a2p which is part of a perl installation which converts awk scripts to perl

a2p  sql_extract.awk> sql_extract.pl

The awk script will extract the SQL and indent it according to the depth parameter of the PARSING IN statements in the trace file.

# Extract the SQL statements from an Oracle Trace file.
#
# 11 March 2015.
#
# Scan for the PARSING IN CURSOR line.
# Print out each line after that, until you hit the END OF STMT line.
# Easy?

BEGIN {}
END {}

/^PARSING IN CURSOR/ {  \
        depth = substr($6, index($6, "=") + 1)
        not_at_end_yet = 1
        while (not_at_end_yet) {
                getline tmp
                if (tmp == "END OF STMT") {
                        not_at_end_yet = 0
                        break
                }
                if (depth == 0)
                        printf "%s\n", tmp
                else
                        printf "%*s %s\n", (depth*4), " ", tmp
        }
        printf "\n"
}

Execute as follows:

 awk -f  sql_extract.awk  tracefile_name.trc output_file.txt

and the a2p command gives the following perl script

#!/usr/bin/perl

eval 'exec /usr/bin/perl -S $0 ${1+"$@"}'

if $running_under_some_shell;

# this emulates #! processing on NIH machines.

# (remove #! line above if indigestible)

eval '$'.$1.'$2;' while $ARGV[0] =~ /^([A-Za-z_0-9]+=)(.*)/ && shift;

# process any FOO=bar switches

# Extract the SQL statements from an Oracle Trace file.

#

# Scan for the PARSING IN CURSOR line.

# Print out each line after that, until you hit the END OF STMT line.

# Easy?

$[ = 1;                 # set array base to 1

while (<>) {

($Fld1,$Fld2,$Fld3,$Fld4,$Fld5,$Fld6) = split(' ', $_, -1);

if (/^PARSING IN CURSOR/) {

$depth = substr($Fld6, index($Fld6, '=') + 1);

$not_at_end_yet = 1;

while ($not_at_end_yet) {

$tmp = &Getline1();

if ($tmp eq 'END OF STMT') {

$not_at_end_yet = 0;

last;

}

if ($depth == 0) {

printf "%s\n", $tmp;

}

else {

printf "%*s %s\n", ($depth * 4), ' ', $tmp;

}

}

printf "\n";

}

}

sub Getline1 {

local($_);

if ($getline_ok = (($_ = <>) ne '')) {

;

}

$_;

}

So an interesting little exercise and another couple of tools in my armourery – tkprof infile, an awk script and how to convert awk to perl

Advertisements

2 Responses to “Getting sql statements out of a trace file”

  1. Cheers for sharing John. Useful couple of tips in there!

    Cheers,
    Rich

  2. zws said

    tkprof xxx.trc record=aa.sql
    it only sql,not record recursive calls sql.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: