Clean the buffer cache :
SQL> alter system flush buffer_cache;Set the session in trace mode :
SQL> alter system set events 'sql_trace level=12' ;Do your test...
Set the session out of the trace mode :
SQL> alter system set events 'sql_trace off' ;
Know look for the traces using adrci tool :
&>: adrci ADRCI: Release 11.2.0.1.0 - Production on Fri Feb 24 11:33:23 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ADR base = "/opt/oracle" adrci> show home ADR Homes: diag/tnslsnr/vgerndpud133/listener_ota4g diag/tnslsnr/vgerndpud133/ota4g diag/tnslsnr/vgerndpud133/listener diag/rdbms/test/TEST adrci> set home diag/rdbms/test/TEST adrci> show tracefiles %ora% -rt 24-FEB-12 10:56:00 diag/rdbms/test/TEST/trace/TEST_ora_5194.trc 24-FEB-12 10:55:20 diag/rdbms/test/TEST/trace/TEST_ora_19558.trc 24-FEB-12 10:55:16 diag/rdbms/test/TEST/trace/TEST_ora_25982.trc 21-FEB-12 17:36:29 diag/rdbms/test/TEST/trace/TEST_ora_5093.trc 21-FEB-12 17:36:24 diag/rdbms/test/TEST/trace/TEST_ora_5050.trc 21-FEB-12 17:35:49 diag/rdbms/test/TEST/trace/TEST_ora_5007.trc 21-FEB-12 17:35:33 diag/rdbms/test/TEST/trace/TEST_ora_4992.trc 21-FEB-12 17:26:49 diag/rdbms/test/TEST/trace/TEST_ora_4202.trc 21-FEB-12 17:21:27 diag/rdbms/test/TEST/trace/TEST_ora_3741.trc 21-FEB-12 17:21:21 diag/rdbms/test/TEST/trace/TEST_ora_3700.trc 12-JAN-12 17:44:07 diag/rdbms/test/TEST/incident/incdir_117854/TEST_ora_8736_i117854.trc 12-JAN-12 10:31:11 diag/rdbms/test/TEST/incident/incdir_117846/TEST_ora_18962_i117846.trc adrci> view TEST_ora_5194.trc Trace file /opt/oracle/diag/rdbms/test/TEST/trace/TEST_ora_5194.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning option ORACLE_HOME = /opt/oracle/11.2.0 System name: Linux Node name: vgerndpud133 Release: 2.6.18-92.el5 Version: #1 SMP Tue Apr 29 13:16:12 EDT 2008 Machine: i686 Instance name: TEST Redo thread mounted by this instance: 1 Oracle process number: 25 Unix process pid: 5194, image: oracle@vgerndpud133 *** 2012-02-24 10:53:49.389 *** SESSION ID:(135.251) 2012-02-24 10:53:49.389 *** CLIENT ID:() 2012-02-24 10:53:49.389 *** SERVICE NAME:(SYS$USERS) 2012-02-24 10:53:49.389 *** MODULE NAME:(emagent_SQL_oracle_database) 2012-02-24 10:53:49.389 *** ACTION NAME:(UserLocks) 2012-02-24 10:53:49.389 WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1330077229389510 WAIT #1: nam='SQL*Net message from client' ela= 186 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1330077229390167 CLOSE #1:c=0,e=10,dep=0,type=3,tim=1330077229390205 =====================
For more reference on event for tracing purposes : How To Use The New 11g Events++ Syntax For Easier SQL Tracing Of Datapump Operations? [ID 813737.1]
How to trace fast user session :
See here under an extract from dba-village @http://www.dba-village.com/village/dvp_tips.TipDetails?TipIdA=3764
If you have to trace a "fast" user session, (for example, initiated by clicking on a button on the GUI of an application), you don't have enough time to query V$SESSION to initiate a trace for the session. A very easy way is to use a database "on logon" trigger; here is an example: CREATE OR REPLACE TRIGGER ON_LOGON_SCOTT AFTER LOGON ON DATABASE WHEN ( USER = 'SCOTT' ) BEGIN execute immediate 'alter session set events ''10046 trace name context forever, level 12'''; EXCEPTION WHEN OTHERS THEN NULL; END; Remarks: 1) The “exception” block is of utmost importance to avoid big trouble if something goes wrong with the trigger. 2) The trigger can contain more conditions (for example: if sysdate between XXX and YYY) 3) Once the trigger is enabled, all new sessions of the user will be traced. To suspend this behaviour momentarily, just perform: ALTER TRIGGER ON_LOGON_SCOTT DISABLE; 4) Suggestion: when not necessary anymore, drop the trigger instead of keeping it “disabled” forever (maybe somebody would re-enable it by mistake): DROP TRIGGER ON_LOGON_SCOTT;See also : http://www.oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof.php http://www.dba-village.com/village/dvp_tips.TipDetails?TipIdA=3764 http://guyharrison.typepad.com/oracleguy/2006/09/10g_tracing_qui.html http://psoug.org/reference/dbms_monitor.html http://dbasolutions.wikispaces.com/How+to+set+trace+on+SQLPLUS Trace Analyzer :https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=367664814786373&id=224270.1&_afrWindowMode=0&_adf.ctrl-state=4et2sref2_4 List of Oracle event codes :
Code Description 10000 Controlfile debug event, name 'control_file' 10001 Controlfile crash event1 10002 Controlfile crash event2 10003 Controlfile crash event3 10004 Controlfile crash event4 10005 Trace latch operations for debugging 10006 Testing - block recovery forced 10007 Log switch debug crash after new log select, thread %s 10008 Log switch debug crash after new log header write, thread %s 10009 Log switch debug crash after old log header write, thread %s 10010 Begin Transaction 10011 End Transaction 10012 Abort Transaction 10013 Instance Recovery 10014 Roll Back to Save Point 10015 Undo Segment Recovery 10016 Undo Segment extend 10017 Undo Segment Wrap 10018 Data Segment Create 10019 Data Segment Recovery 10020 Partial link restored to linked list (KSG) 10021 Latch cleanup for state objects (KSS) 10022 Trace ktsgsp 10023 Create Save Undo Segment 10024 Write to Save Undo 10025 Extend Save Undo Segment 10026 Apply Save Undo 10027 Latch cleanup for enqueue locks (KSQ) 10028 Latch cleanup for enqueue resources (KSQ) 10029 Session logon (KSU) 10030 Session logoff (KSU) 10031 Sort debug event (S*) 10032 Sort statistics (SOR*) 10033 Sort run information (SRD*/SRS*) 10034 Access path analysis (APA*) 10035 Parse SQL statement (OPIPRS) 10036 Create remote row source (QKANET) 10037 Allocate remote row source (QKARWS) 10038 Dump row source tree (QBADRV) 10039 Type checking (OPITCA) 10040 Dirty cache list 10041 Dump undo records skipped 10042 Trap error during undo application 10043 Check consistency of owner/waiter/converter lists in KSQ 10044 Free list undo operations 10045 Free list update operations - ktsrsp, ktsunl 10046 Enable SQL statement timing 10047 Trace switching of sessions 10048 Undo segment shrink 10049 Protect library cache memory heaps 10050 Sniper trace 10051 Trace OPI calls 10052 Don't clean up obj$ 10053 CBO Enable optimizer trace 10054 Trace UNDO handling in MLS 10055 Trace UNDO handing 10056 Dump analyze stats (kdg) 10057 Suppress file names in error messages 10058 Use table scan cost in tab$.spare1 10059 Simulate error in logfile create/clear 10060 CBO Enable predicate dump 10061 Disable SMON from cleaning temp segment 10062 Disable usage of OS Roles in osds 10063 Disable usage of DBA and OPER privileges in osds 10064 Thread enable debug crash level %s, thread %s 10065 Limit library cache dump information for state object dump 10066 Simulate failure to verify file 10067 Force redo log checksum errors - block number 10068 Force redo log checksum errors - file number 10069 Trusted Oracle test event 10070 Force datafile checksum errors - block number 10071 Force datafile checksum errors - file number 10072 Protect latch recovery memory 10073 Have PMON dump info before latch cleanup 10074 Default trace function mask for kst 10075 CBO Disable outer-join to regular join conversion 10076 CBO Enable cartesian product join costing 10077 CBO Disable view-merging optimization for outer-joins 10078 CBO Disable constant predicate elimination optimization 10079 Trace data sent/received via SQL*Net 10080 Dump a block on a segment list which cannot be exchanged 10081 Segment High Water Mark has been advanced 10082 Free list head block is the same as the last block 10083 A brand new block has been requested from space management 10084 Free list becomes empty 10085 Free lists have been merged 10086 CBO Enable error if kko and qka disagree on oby sort 10087 Disable repair of media corrupt data blocks 10088 CBO Disable new NOT IN optimization 10089 CBO Disable index sorting 10090 Invoke other events before crash recovery 10091 CBO Disable constant predicate merging 10092 CBO Disable hash join 10093 CBO Enable force hash joins 10094 Before resizing a data file 10095 Dump debugger commands to trace file 10096 After the cross instance call when resizing a data file 10097 After generating redo when resizing a data file 10098 After the OS has increased the size of a data file 10099 After updating the file header with the new file size 10100 After the OS has decreased the size of a data file 10101 Atomic redo write recovery 10102 Switch off anti-joins 10103 CBO Disable hash join swapping 10104 Dump hash join statistics to trace file 10105 CBO Enable constant pred trans and MPs w WHERE-clause 10106 CBO Disable evaluating correlation pred last for NOT IN 10107 CBO Always use bitmap index 10108 CBO Don't use bitmap index 10109 CBO Disable move of negated predicates 10110 CBO Try index rowid range scans 10111 Bitmap index creation switch 10112 Bitmap index creation switch 10113 Bitmap index creation switch 10114 Bitmap index creation switch 10115 CBO Bitmap optimization use maximal expression 10116 CBO Bitmap optimization switch 10117 CBO Disable new parallel cost model 10118 CBO Enable hash join costing 10119 QKA Disable GBY sort elimination 10120 Generate relative file # different from absolute 10121 CBO Don't sort bitmap chains 10122 Disable transformation of count(col) to count(*) 10123 QKA Disable Bitmap And-EQuals 10124 Force creation of segmented arrays by kscsAllocate 10125 Disable remote sort elimination 10126 Debug oracle java xa 10127 Disable remote query block operation 10128 Dump Partition Pruning Information 10129 Alter histogram lookup for remote queries 10130 Sort disable readaheads 10131 Use v$sql_plan code path for explain plan 10132 Dump plan after compilation 10133 Testing for SQL Memory Management 10134 Tracing for SQL Memory Management for session 10135 CBO do not count 0 rows partitions 10136 CBO turn off fix for bug 1089848 10137 CBO turn off fix for bug 1344111 10138 CBO turn off fix for bug 1577003 10139 CBO turn off fix for bug 1386119 10140 CBO turn off fix for bug 1332980 10141 CBO disable additional keys for inlist in bitmap optimization 10142 CBO enable dynamic selectivity estimation 10143 CBO force dynamic selectivity estimation (if enabled) 10145 Test auditing network errors 10146 Enable Oracle TRACE collection 10148 Use pre-7.3.3 random generator 10149 Allow the creation of constraints with illegal date constants 10150 Import exceptions 10151 Force duplicate dependency removal 10152 CBO don't consider function costs in plans 10153 Switch to use public synonym if private one does not translate 10154 Switch to disallow synonyms in DDL statements 10155 CBO disable generation of transitive OR-chains 10156 CBO disable index fast full scan 10157 CBO disable index access path for in-list 10158 CBO preserve predicate order in post-filters 10159 CBO disable order-by sort pushdown into domain indexes 10160 CBO disable use of join index 10161 CBO recursive semi-join on/off-switch 10162 CBO join-back elimination on/off-switch 10163 CBO join-back elimination on/off-switch 10164 CBO disable subquery-adjusted cardinality fix 10165 Mark session to be aborted during shutdown normal 10166 Trace long operation statistics updates 10167 CBO use old index MIN/MAX optimization 10168 CBO disable single-table predicate predicate generation 10169 CBO disable histograms for multi partitions 10170 CBO use old bitmap costing 10171 CBO disable transitive join predicates 10172 CBO force hash join back 10173 CBO no constraint-based join-back elimination 10174 View join-back elimination switch 10175 CBO star transformation switch 10176 CBO colocated join switch 10177 CBO colocated join switch 10178 CBO turn off hash cluster filtering through memcmp 10179 CBO turn off transitive predicate replacement 10180 Temp table transformation print error messages 10181 CBO disable multi-column in-list processing 10182 CBO disable generation of implied predicates 10183 CBO disable cost rounding 10184 CBO disable OR-exp if long inlist on bitmap column 10185 CBO force index joins 10186 CBO disable index join 10187 CBO additional index join switch 10188 "CBO additional index join switch 10189 CBO turn off FFS null fix 10190 Analyze use old frequency histogram collection and density 10191 Avoid conversion of in-lists back to OR-expanded form 10192 Nopushdown when number of groups exceed number of rows 10193 Force repeatable sampling with specified seed 10194 CBO disable new LIKE selectivity heuristic 10195 CBO don't use check constraints for transitive predicates 10196 CBO disable index skip scan 10197 CBO force index skip scan 10198 Check undo record 10199 Set parameter in session 10200 Consistent read buffer status 10201 Consistent read undo application 10202 Consistent read block header 10203 Block cleanout 10204 Signal recursive extend 10205 Row cache debugging 10206 Transaction table consistent read 10207 Consistent read transactions' status report 10208 Consistent read loop check 10209 Enable simulated error on controlfile 10210 Check data block integrity 10211 Check index block integrity 10212 Check cluster integrity 10213 Crash after controlfile write 10214 Simulate write errors on controlfile 10215 Simulate read errors on controlfile 10216 Dump controlfile header 10217 Debug sequence numbers 10218 Dump uba of applied undo 10219 Monitor multi-pass row locking 10220 Show updates to the transaction table 10221 Show changes done with undo 10222 Row cache 10223 Transaction layer - turn on verification codes 10224 Index block split/delete trace 10225 Free/used extent row cache 10226 Trace CR applications of undo for data operations 10227 Verify (multi-piece) row structure 10228 Trace application of redo by kcocbk 10229 Simulate I/O error against datafiles 10230 Check redo generation by copying before applying 10231 Skip corrupted blocks on _table_scans_ 10232 Dump corrupted blocks symbolically when kcbgotten 10233 Skip corrupted blocks on index operations 10234 Trigger event after calling kcrapc to do redo N times 10235 Check memory manager internal structures 10236 Library cache manager 10237 Simulate ^C (for testing purposes) 10238 Instantiation manager 10239 Multi-instance library cache manager 10240 Dump dba's of blocks that we wait for 10241 Remote SQL execution tracing/validation 10242 Suppress OER 2063 (for testing distrib w/o different error log) 10243 Simulated error for test %s of K2GTAB latch cleanup 10244 Make tranids in error msgs print as 0.0.0 (for testing) 10245 Simulate lock conflict error for testing PMON 10246 Print trace of PMON actions to trace file 10247 Turn on scgcmn tracing. (VMS ONLY) 10248 Turn on tracing for dispatchers 10249 Turn on tracing for multi-stated servers 10250 Trace all allocate and free calls to the topmost SGA heap 10251 Check consistency of transaction table and undo block 10252 Simulate write error to data file header 10253 Simulate write error to redo log 10254 Trace cross-instance calls 10255 Pl/sql parse checking 10256 Turn off shared server load balancing 10257 Trace shared server load balancing 10258 Force shared servers to be chosen round-robin 10259 Get error message text from remote using explicit call 10260 Trace calls to SMPRSET (VMS ONLY) 10261 Limit the size of the PGA heap 10262 Don't check for memory leaks 10263 Don't free empty PGA heap extents 10264 Collect statistics on context area usage (x$ksmcx) 10265 Keep random system generated output out of error messages 10266 Trace OSD stack usage 10267 Inhibit KSEDMP for testing 10268 Don't do forward coalesce when deleting extents 10269 Don't do coalesces of free space in SMON 10270 Debug shared cursors 10271 Distributed transaction after COLLECT 10272 Distributed transaction before PREPARE 10273 Distributed transaction after PREPARE 10274 Distributed transaction before COMMIT 10275 Distributed transaction after COMMIT 10276 Distributed transaction before FORGET 10277 Cursor sharing (or not) related event (used for testing) 10278 Internal testing 10279 Simulate block corruption in kdb4chk 10280 Internal testing - segmentation fault during crash recovery 10281 Maximum time to wait for process creation 10282 Inhibit signalling of other backgrounds when one dies 10283 Simulate asynch I/O never completing 10284 Simulate zero/infinite asynch I/O buffering 10285 Simulate controlfile header corruption 10286 Simulate controlfile open error 10287 Simulate archiver error 10288 Do not check block type in ktrget 10289 Do block dumps to trace file in hex rather than fromatted 10290 Kdnchk - checkvalid event - not for general purpose use. 10291 Die in tbsdrv to test controlfile undo 10292 Dump uet entries on a 1561 from dtsdrv 10293 Dump debugging information when doing block recovery 10294 Enable PERSISTENT DLM operations on non-compliant systems 10295 Die after file header update durning cf xact 10296 Disable ORA-379 10297 Customize dictionary object number cache 10298 Ksfd i/o tracing 10299 Trace prefetch tracking decisions made by CKPT 10300 Disable undo compatibility check at database open 10301 Enable LCK timeout table consistency check 10302 Trace create or drop internal trigger 10303 Trace loading of library cache for internal triggers 10304 Trace replication trigger 10305 Trace updatable materialized view trigger 10306 Trace materialized view log trigger 10307 Trace RepCat execution 10308 Replication testing event 10309 Trigger Debug event 10310 Trace synchronous change table trigger 10311 Disable Flashback Table Timestamp checking 10312 Allow disable to log rows into the mapping table 10319 Trace PGA statistics maintenance 10320 Enable data layer (kdtgrs) tracing of space management calls 10321 Datafile header verification debug failure. 10323 Before committing an add datafile command 10324 Enable better checking of redo logs errors 10325 Trace control file record section expand and shrink operations 10326 Clear logfile debug crash at %s, log %s 10327 Simulate ORA-00235 error for testing 10328 Disable first-to-mount split-brain error, for testing 10329 Simulate lost write, test detection by two-pass recovery 10330 Clear MTTR statistics in checkpoint progress record 10331 Simulate resilvering during recovery 10332 Force ALTER SYSTEM QUIESCE RESTRICTED command to fail 10336 Do remote object transfer using remote SQL 10337 Enable padding owner name in slave sql 10340 Buffer queues sanity check for corrupted buffers 10341 Simulate out of PGA memory in DBWR during object reuse 10342 Raise unknown exception in ACQ_ADD when checkpointing 10343 Raise an out of memory exception-OER 4031 in ACQ_ADD 10344 Simulate kghxal returning 0 in ACQ_ADD but no exception 10345 Validate queue when linking or unlinking a buffer 10346 Check that all buffers for checkpoint have been written 10347 Dump active checkpoint entries and checkpoint buffers 10348 Test abnormal termination of process initiating file checkpoint 10349 Do not allow ckpt to complete 10350 Simulate more than one object & tsn id in object reuse 10351 Size of slots 10352 Report direct path statistics 10353 Number of slots 10354 Turn on direct read path for parallel query 10355 Turn on direct read path for scans 10356 Turn on hint usage for direct read 10357 Turn on debug information for direct path 10359 Turn off updates to control file for direct writes 10360 Enable dbwr consistency checking 10365 Turn on debug information for adaptive direct reads 10370 Parallel query server kill event 10371 Disable TQ hint 10372 Parallel query server kill event proc 10373 Parallel query server kill event 10374 Parallel query server interrupt (validate lock value) 10375 Turn on checks for statistics rollups 10376 Turn on table queue statistics 10377 Turn off load balancing 10378 Force hard process/range affinity 10379 Direct read for rowid range scans (unimplemented) 10380 Kxfp latch cleanup testing event 10381 Kxfp latch cleanup testing event 10382 Parallel query server interrupt (reset) 10383 Auto parallelization testing event 10384 Parallel dataflow scheduler tracing 10385 Parallel table scan range sampling method 10386 Parallel SQL hash and range statistics 10387 Parallel query server interrupt (normal) 10388 Parallel query server interrupt (failure) 10389 Parallel query server interrupt (cleanup) 10390 Trace parallel query slave execution 10391 Trace PX granule allocation/assignment 10392 Parallel query debugging bits 10393 Print parallel query statistics 10394 Generate a fake load to test adaptive and load balancing 10395 Adjust sample size for range table queues 10396 Circumvent range table queues for queries 10397 Suppress verbose parallel coordinator error reporting 10398 Enable timeouts in parallel query threads 10399 Trace buffer allocation 10400 Turn on system state dumps for shutdown debugging 10401 Turn on IPC (ksxp) debugging 10402 Turn on IPC (skgxp) debugging 10403 Fake CPU number for default degree of parallelism 10404 Crash dbwr after write 10405 Emulate broken mirrors 10406 Enable datetime TIMESTAMP, INTERVAL datatype creation 10407 Enable datetime TIME datatype creation 10408 Disable OLAP builtin window function usage 10410 Trigger simulated communications errors in KSXP 10411 Simulate errors in IMR 10412 Trigger simulated errors in CGS/CM interface 10425 Enable global enqueue service open event trace 10426 Enable global enqueue service convert event trace 10427 Enable global enqueue service traffic controller event trace 10428 Enable tracing of global enqueue service distributed resource 10429 Enable tracing of global enqueue service IPC calls 10430 Enable tracing of global enqueue service AST calls 10431 Enable verification messages on pi consistency 10432 Enable tracing of global cache service fusion calls 10433 Global enqueue service testing event 10434 Enable tracing of global enqueue service muliple LMS 10435 Enable tracing of global enqueue service deadlock detetction 10450 Signal ctrl-c in kdddca (drop column) after n rows 10500 Turn on traces for SMON 10510 Turn off SMON check to offline pending offline rollback segment 10511 Turn off SMON check to cleanup undo dictionary 10512 Turn off SMON check to shrink rollback segments 10515 Turn on event to use physical cleanout 10550 Signal error during create as select/create index after n rows 10560 Block type '%s' 10561 Block type '%s', data object# %s 10562 Error occurred while applying redo to data block (file# %s, block# %s) 10563 Test recovery had to corrupt data block (file# %s, block# %s) in order to proceed 10564 Tablespace %s 10565 Another test recovery session is active 10566 Test recovery has used all the memory it can use 10567 Redo is inconsistent with data block (file# %s, block# %s) 10568 Failed to allocate recovery state object: out of SGA memory 10570 Test recovery complete 10571 Test recovery canceled 10572 Test recovery canceled due to errors 10573 Test recovery tested redo from change %s to %s 10574 Test recovery did not corrupt any data block 10575 Give up restoring recovered datafiles to consistent state: out of memory 10576 Give up restoring recovered datafiles to consistent state: some error occurred 10577 Can not invoke test recovery for managed standby database recovery 10578 Can not allow corruption for managed standby database recovery 10579 Can not modify control file during test recovery 10580 Can not modify datafile header during test recovery 10581 Can not modify redo log header during test recovery 10582 The control file is not a backup control file 10583 Can not recovery file %s renamed as missing during test recovery 10584 Can not invoke parallel recovery for test recovery 10585 Test recovery can not apply redo that may modify control file 10586 Test recovery had to corrupt 1 data block in order to proceed 10587 Invalid count for ALLOW n CORRUPTION option 10588 Can only allow 1 corruption for normal media/standby recovery 10589 Test recovery had to corrupt %s data blocks in order to proceed 10590 Kga (argus debugger) test flags 10591 Kga (argus debugger) test flags 10592 Kga (argus debugger) test flags 10593 Kga (argus debugger) test flags 10594 Kga (argus debugger) test flags 10595 Kga (argus debugger) test flags 10596 Kga (argus debugger) test flags 10597 Kga (argus debugger) test flags 10598 Kga (argus debugger) test flags 10599 Kga (argus debugger) test flags 10600 Check cursor frame allocation 10601 Turn on debugging for cursor_sharing (literal replacement) 10602 Cause an access violation (for testing purposes) 10603 Cause an error to occur during truncate (for testing purposes) 10604 Trace parallel create index 10605 Enable parallel create index by default 10606 Trace parallel create index 10607 Trace index rowid partition scan 10608 Trace create bitmap index 10609 Trace for array index insertion 10610 Trace create index pseudo optimizer 10611 Causes migration to fail - testing only 10612 Prints debug information for auto-space managed segments 10613 Prints debug information for auto-space managed segments 10614 Operation not allowed on this segment 10615 Invalid tablespace type for temporary tablespace 10616 Operation not allowed on this tablespace 10617 Cannot create rollback segment in this tablespace 10618 Operation not allowed on this segment 10619 Avoid assertions when possible 10620 Operation not allowed on this segment 10621 Data block does not belong to the segment 10622 Test/trace online index (re)build 10623 Enable Index range scan Prefetch - testing only 10650 Disable cache-callback optimisation 10651 Incorrect file number block number specified 10666 Do not get database enqueue name 10667 Cause sppst to check for valid process ids 10690 Set shadow process core file dump type (Unix only) 10691 Set background process core file type (Unix only) 10700 Alter access violation exception handler 10701 Dump direct loader index keys 10702 Enable histogram data generation 10703 Simulate process death during enqueue get 10704 Print out information about what enqueues are being obtained 10705 Print Out Tracing information for every I/O done by ODSs 10706 Print out information about global enqueue manipulation 10707 Simulate process death for instance registration 10708 Print out Tracing information for skxf multi instance comms 10709 Enable parallel instances in create index by default 10710 Trace bitmap index access 10711 Trace bitmap index merge 10712 Trace bitmap index or 10713 Trace bitmap index and 10714 Trace bitmap index minus 10715 Trace bitmap index conversion to rowids 10716 Trace bitmap index compress/decompress 10717 Trace bitmap index compaction trace for index creation 10718 Event to disable automatic compaction after index creation 10719 Trace bitmap index dml 10720 Trace db scheduling 10721 Internal testing - temp table transformation 10722 Set parameters for CPU frequency calculation (debug) 10723 Internal testing - release buffer for buffer cache shrink 10730 Trace row level security policy predicates 10731 Dump SQL for CURSOR expressions 10740 Disables fix for bug 598861 10750 Test rollback segment blksize guessing for index array insert 10800 Disable Smart Disk scan 10801 Enable Smart Disk trace 10802 Reserved for Smart Disk 10803 Write timing statistics on cluster database recovery scan 10804 Reserved for ksxb 10806 Switch to 7.3 mode when detaching sessions 10807 Disable user id check when switching to a global transaction 10810 Trace snapshot too old 10811 Trace block cleanouts 10812 Trace Consistent Reads 10830 Trace group by sort row source 10841 Default un-inintialized charact set form to SQLCS_IMPLICIT 10850 Enable time manager tracing 10851 Allow Drop command to drop queue tables 10852 Enable dumping of the AQ statistics hash table 10853 Event for AQ statistics latch cleanup testing 10856 Disable AQ propagator from using streaming 10857 Force AQ propagator to use two-phase commit 10858 Crash the AQ propagator at different stages of commit 10859 Disable updates of message retry count 10860 Event for AQ admin disable new name parser 10861 Disable storing extended message properties 10862 Resolve default queue owner to current user in enqueue/dequeue 10900 Extent manager fault insertion event #%s 10902 Disable seghdr conversion for ro operation 10903 Force tablespaces to become locally managed 10904 Allow locally managed tablespaces to have user allocation 10905 Do cache verification (kcbcxx) on extent allocation 10906 Unable to extend segment after insert direct load 10907 Trace extent management events 10908 Trace temp tablespace events 10909 Trace free list events 10924 Import storage parse error ignore event 10925 Trace name context forever 10926 Trace name context forever 10927 Trace name context forever 10928 Trace name context forever 10929 Trace name context forever 10930 Trace name context forever 10931 Trace name context forever 10932 Trace name context forever 10933 Trace name context forever 10934 Reserved. Used only in version 7.x. 10935 Reserved. Used only in version 7.x. 10936 Trace name context forever 10937 Trace name context forever 10938 Trace name context forever 10939 Trace name context forever 10940 Trace name context forever 10941 Trace name context forever 10943 Trace name context forever 10944 Trace name context forever 10945 Trace name context forever 10975 Trace execution of parallel propagation 10976 Internal package related tracing 10977 Trace event for RepAPI 10979 Trace flags for join index implementation 10980 Prevent sharing of parsed query during Materialized View query generation 10981 Dscn computation-related event in replication 10982 Event to turn off CDC-format MV Logs 10983 Event to enable Create_Change_Table debugging 10984 Subquery materialized view-related event 10985 Event for NULL refresh of materialized views 10986 Do not use HASH_AJ in refresh 10987 Event for the support of caching table with object feature 10988 Event to get exclusive lock during materialized view refresh in IAS 10989 Event to internally create statistics MV 10999 Do not get database enqueue name 10999 End Pseudo-error debugging eventsHow to trace my session :
First, get my session id :
SQL> select distinct sid from v$mystat;
SID
----------
29
Update the tracefile name to contains a specific word.
The trace file will be more easy to identify because it would have the following format : <SID>_ora_<pid>_<my_word>.trc
SQL> alter session set tracefile_identifier="FMOSSE_SESSION"; Session altered.So the trace file will have the following format : <SID>_ora_<pid>_FMOSSE_SESSION.trc Under sys user, set my session in trace mode:
SQL> exec dbms_monitor.session_trace_enable( session_id=>29, waits=>TRUE, binds=>TRUE);Do your stuff here.. Desactivate the trace for the session :
SQL> exec dbms_monitor.session_trace_disable( session_id=>29);Generate a more readable trace file using tkprof:
Linux> tkprof FME_ora_2270_FMOSSE_SESSION.trc /tmp/mytrace.txt explain=sys/<passord for sys> sys=no waits=yes
