Just a method to keep somewhere how to perform some trace when testing a query or a set of queries to known what's happening in the database :
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 events
How 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
