How to get dump or list parameters set at session level?

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters. SQL> alter session set sql_trace=true; Session altered. SQL> alter session set optimizer_mode=first_rows; Session altered. -- connect to session SQL> oradebug setmypid Statement processed. SQL> oradebug dump modified_parameters 1; Statement processed. SQL> oradebug tracefile_name; i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc Contents of…

Example of using tracefile_identifier

Using “tracefile_identifier” Oracle will add that to the name of the user’s trace filename so the file can easily be identified. For example if one needs to trace multiple users session who have different schema or OS user then using “tracefile_identifier” one can identify the trace file specific to a user. Example: The following trigger…

How to turn on debug/trace when running RMAN?

Using the debug option one can turn on trace information, this can be used to troubleshoot an issue. Example: $ORACLE_HOME/bin/rman target / debug trace=/tmp/rman.log — Log file $ head -20 /tmp/rman.log Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 01:46:22 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. ORACLE_HOME = /u01/oracle/product/10.2.0/db_1 System…

How to turn trace and set events using dbms_system?

To turn on trace for a specific event. exec dbms_system.set_ev(<SID>, <serial#>, <event>, 8, ''); Example: Enable trace at level 8 for session id 10046 exec dbms_system.set_ev(12345, 543211, 10046, 8, ''); — To turn off the tracing: exec dbms_system.set_ev(<SID>, <serial#>, <event>, 0, ''); Example: exec dbms_system.set_ev( 1234, 56789, 10046, 0, '');