Home » RDBMS Server » Server Administration » SQL TRACE file generated by imp (V10.2.0.1 on RHEL 4)
| SQL TRACE file generated by imp [message #332146] |
Mon, 07 July 2008 11:26  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
I have inherited an instance which has SQL TRACE enabled; even for imports.
I state this because every import I initiate, a new trace file in udump is generated.
I want to disable the creation of these trace files, but can not find where to do so.
The real problem is this system is very short on disk space and an import generates
a 2+GB tracefile & fills up the volume.
I have issued the command below, but the trace file continue to be generated.
alter system set events ‘10046 trace name context off’;
As far as I know, no logon trigger exists & nothing enables TRACE within glogin.
I started the instance using a pfile which does not contain any line to enable TRACE.
I have never seen this behavior & have exhausted places to look.
I need to stop the trace file from being generated by imp.
Any clues would be most appreciated.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL TRACE file generated by imp [message #334551 is a reply to message #334548] |
Thu, 17 July 2008 01:33  |
aaryaan
Messages: 9
Registered: July 2008
Location: Bangalore
|
Junior Member |
|
|
OP mentioned that trace file is getting generated when he fires import. Like to know what is the content of trace files.
Regards,
Satheesh Babu S
[Edit MC: remove blog link. Put a link to your blog only if it contains an answer to the question, otherwise it is a matter of your profile or MarketPlace forum]
[Updated on: Thu, 17 July 2008 01:44] by Moderator Report message to a moderator |
|
|
|
| Re: SQL TRACE file generated by imp [message #334652 is a reply to message #332146] |
Thu, 17 July 2008 10:17 |
|
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Yes, this is WIDE output, Michel.
I took the cowards way out of this problem.
I added another disk to a different system & imported the data into it.
I will retire this old possessed system & not worry how or why any trace file gets generated.
[oracle@crystaldb udump]$ head -200 orcl_ora_4362.trc
/u01/app/oracle/admin/orcl/udump/orcl_ora_4362.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1
System name: Linux
Node name: crystaldb
Release: 2.6.9-42.EL
Version: #1 Wed Jul 12 23:16:43 EDT 2006
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 4362, image: oracle@crystaldb (TNS V1-V3)
*** SERVICE NAME:(SYS$USERS) 2008-07-07 11:19:24.133
*** SESSION ID:(147.44) 2008-07-07 11:19:24.133
WAIT #0: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1186967543099519
WAIT #0: nam='SQL*Net message from client' ela= 3796 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1186967543121337
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1186967543121491
WAIT #0: nam='SQL*Net message from client' ela= 353 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1186967543121885
WAIT #5: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1186967543121993
WAIT #5: nam='SQL*Net message from client' ela= 628 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1186967543122660
=====================
PARSING IN CURSOR #2 len=83 dep=1 uid=0 oct=3 lid=0 tim=1186967543129036 hv=1709162946 ad='29aebc74'
select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj#=:1
END OF STMT
PARSE #2:c=1000,e=1398,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543129022
BINDS #2:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b72b6ab0 bln=22 avl=03 flg=05
value=2836
EXEC #2:c=2999,e=2658,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543131971
WAIT #2: nam='db file sequential read' ela= 51 file#=1 block#=748 blocks=1 obj#=-1 tim=1186967543132224
WAIT #2: nam='db file sequential read' ela= 9667 file#=1 block#=6619 blocks=1 obj#=-1 tim=1186967543141991
FETCH #2:c=1000,e=10012,p=2,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1186967543142077
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=63 op='TABLE ACCESS BY INDEX ROWID VIEW$ (cr=3 pr=2 pw=0 time=10007 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=99 op='INDEX UNIQUE SCAN I_VIEW1 (cr=2 pr=1 pw=0 time=236 us)'
=====================
PARSING IN CURSOR #2 len=179 dep=1 uid=0 oct=3 lid=0 tim=1186967543144011 hv=2812844157 ad='29af8618'
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
END OF STMT
PARSE #2:c=1000,e=1672,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543144000
BINDS #2:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b72c80e8 bln=22 avl=03 flg=05
value=2836
EXEC #2:c=7999,e=8341,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543152640
WAIT #2: nam='db file sequential read' ela= 1702 file#=1 block#=6317 blocks=1 obj#=-1 tim=1186967543154541
WAIT #2: nam='db file sequential read' ela= 2496 file#=1 block#=6336 blocks=1 obj#=-1 tim=1186967543157142
FETCH #2:c=1000,e=4630,p=2,cr=6,cu=0,mis=0,r=1,dep=1,og=4,tim=1186967543157361
FETCH #2:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1186967543157541
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=6 pr=2 pw=0 time=4656 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=6 pr=2 pw=0 time=4572 us)'
STAT #2 id=3 cnt=1 pid=2 pos=1 obj=92 op='TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=4 pr=2 pw=0 time=4495 us)'
STAT #2 id=4 cnt=1 pid=3 pos=1 obj=122 op='INDEX RANGE SCAN I_DEPENDENCY1 (cr=3 pr=1 pw=0 time=1899 us)'
STAT #2 id=5 cnt=0 pid=2 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=0 pw=0 time=48 us)'
STAT #2 id=6 cnt=0 pid=5 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=32 us)'
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=3 lid=0 tim=1186967543158986 hv=3993603298 ad='29af77c4'
select order#,columns,types from access$ where d_obj#=:1
END OF STMT
PARSE #2:c=1000,e=1097,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543158976
BINDS #2:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b72c80e8 bln=22 avl=03 flg=05
value=2836
EXEC #2:c=2000,e=2377,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543161627
WAIT #2: nam='db file sequential read' ela= 50 file#=1 block#=952 blocks=1 obj#=-1 tim=1186967543161875
WAIT #2: nam='db file sequential read' ela= 1234 file#=1 block#=6392 blocks=1 obj#=-1 tim=1186967543163208
FETCH #2:c=0,e=1552,p=2,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1186967543163285
FETCH #2:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1186967543163399
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=93 op='TABLE ACCESS BY INDEX ROWID ACCESS$ (cr=4 pr=2 pw=0 time=1555 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=124 op='INDEX RANGE SCAN I_ACCESS1 (cr=3 pr=1 pw=0 time=243 us)'
=====================
PARSING IN CURSOR #2 len=348 dep=1 uid=0 oct=3 lid=0 tim=1186967543166330 hv=2512561537 ad='29afd1c4'
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#
END OF STMT
PARSE #2:c=2000,e=2567,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543166319
BINDS #2:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b72c8014 bln=22 avl=03 flg=05
value=2836
EXEC #2:c=3998,e=3920,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543170613
FETCH #2:c=0,e=238,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1186967543170948
FETCH #2:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1186967543171017
FETCH #2:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1186967543171082
STAT #2 id=1 cnt=2 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=3 pr=0 pw=0 time=251 us)'
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=21 op='TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=140 us)'
STAT #2 id=3 cnt=1 pid=2 pos=1 obj=3 op='INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=67 us)'
=====================
PARSING IN CURSOR #2 len=37 dep=1 uid=0 oct=3 lid=0 tim=1186967543176662 hv=1398610540 ad='29aead70'
select text from view$ where rowid=:1
END OF STMT
PARSE #2:c=5999,e=5269,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543176649
BINDS #2:
kkscoacd
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=b72c6974 bln=16 avl=16 flg=05
value=000019DB.0009.0001
EXEC #2:c=2000,e=2042,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543178939
FETCH #2:c=0,e=95,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1186967543179125
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=63 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=67 us)'
=====================
PARSING IN CURSOR #2 len=53 dep=1 uid=0 oct=3 lid=0 tim=1186967543180091 hv=2195068792 ad='29ac31f4'
select timestamp, flags from fixed_obj$ where obj#=:1
END OF STMT
PARSE #2:c=0,e=453,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543180080
BINDS #2:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b72c6560 bln=22 avl=06 flg=05
value=4294951133
EXEC #2:c=999,e=1784,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543182019
FETCH #2:c=0,e=52,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1186967543182125
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=706 op='TABLE ACCESS BY INDEX ROWID FIXED_OBJ$ (cr=2 pr=0 pw=0 time=67 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=707 op='INDEX UNIQUE SCAN I_FIXED_OBJ$_OBJ# (cr=2 pr=0 pw=0 time=47 us)'
=====================
PARSING IN CURSOR #7 len=53 dep=2 uid=0 oct=3 lid=0 tim=1186967543187450 hv=2195068792 ad='29ac31f4'
select timestamp, flags from fixed_obj$ where obj#=:1
END OF STMT
PARSE #7:c=0,e=129,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1186967543187431
BINDS #7:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b72c637c bln=22 avl=06 flg=05
value=4294951329
EXEC #7:c=0,e=278,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1186967543187885
FETCH #7:c=0,e=52,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1186967543187976
STAT #7 id=1 cnt=0 pid=0 pos=1 obj=706 op='TABLE ACCESS BY INDEX ROWID FIXED_OBJ$ (cr=2 pr=0 pw=0 time=69 us)'
STAT #7 id=2 cnt=0 pid=1 pos=1 obj=707 op='INDEX UNIQUE SCAN I_FIXED_OBJ$_OBJ# (cr=2 pr=0 pw=0 time=48 us)'
=====================
PARSING IN CURSOR #8 len=53 dep=3 uid=0 oct=3 lid=0 tim=1186967543193113 hv=2195068792 ad='29ac31f4'
select timestamp, flags from fixed_obj$ where obj#=:1
END OF STMT
PARSE #8:c=0,e=95,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,tim=1186967543193093
BINDS #8:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b72c6198 bln=22 avl=06 flg=05
value=4294951132
EXEC #8:c=0,e=237,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,tim=1186967543193509
FETCH #8:c=0,e=101,p=0,cr=3,cu=0,mis=0,r=1,dep=3,og=4,tim=1186967543193648
STAT #8 id=1 cnt=1 pid=0 pos=1 obj=706 op='TABLE ACCESS BY INDEX ROWID FIXED_OBJ$ (cr=3 pr=0 pw=0 time=109 us)'
STAT #8 id=2 cnt=1 pid=1 pos=1 obj=707 op='INDEX UNIQUE SCAN I_FIXED_OBJ$_OBJ# (cr=2 pr=0 pw=0 time=52 us)'
=====================
PARSING IN CURSOR #7 len=45 dep=2 uid=0 oct=3 lid=0 tim=1186967543194938 hv=2584255609 ad='298584ec'
select inst_id,parameter, value from x$option
END OF STMT
PARSE #7:c=6999,e=6595,p=0,cr=3,cu=0,mis=1,r=0,dep=2,og=4,tim=1186967543194926
=====================
PARSING IN CURSOR #2 len=76 dep=1 uid=0 oct=3 lid=0 tim=1186967543196415 hv=2293946307 ad='29858880'
select PARAMETER , VALUE from GV$OPTION where inst_id = USERENV('Instance')
END OF STMT
PARSE #2:c=13998,e=13895,p=0,cr=5,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543196402
=====================
PARSING IN CURSOR #8 len=169 dep=1 uid=0 oct=3 lid=0 tim=1186967543199428 hv=1173719687 ad='29ae7484'
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#
END OF STMT
PARSE #8:c=2000,e=2152,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543199416
BINDS #8:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b72c65a4 bln=22 avl=03 flg=05
value=2836
EXEC #8:c=2999,e=2944,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543202626
WAIT #8: nam='db file sequential read' ela= 50 file#=1 block#=7562 blocks=1 obj#=-1 tim=1186967543202874
FETCH #8:c=0,e=264,p=1,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1186967543202992
STAT #8 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT GROUP BY (cr=2 pr=1 pw=0 time=323 us)'
STAT #8 id=2 cnt=0 pid=1 pos=1 obj=57 op='TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=1 pw=0 time=248 us)'
STAT #8 id=3 cnt=0 pid=2 pos=1 obj=103 op='INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=1 pw=0 time=230 us)'
=====================
PARSING IN CURSOR #7 len=151 dep=1 uid=0 oct=3 lid=0 tim=1186967543205407 hv=4139184264 ad='29ae6b5c'
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#
END OF STMT
PARSE #7:c=2000,e=2147,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1186967543205395
BINDS #7:
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Dec 01 22:26:37 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
