1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Making trace files available

Let's start with some examples of how to check trace for another session that is connected to the database.

Now find the SID and SERIAL# of the other session

We are using a simple example and the session we are looking for is for the user SCOTT and we are logged into this session with AS SYSDBA. We need to be logged in as SYS or AS SYSDBA so that we can access the packages DBMS_SUPPORT and DBMS_SYSTEM needed to set trace in another session or in our own session. Again as with the first example about access to v$parameter a user with access to the views V$SESSION and V$PROCESS is needed. First lets find the SID and SERIAL#

SQL> connect / as sysdba
SQL> col sid for 999999
SQL> col serial# for 999999
SQL> col username for a20
SQL> col osuser for a20
SQL> select s.sid,s.serial#,s.username,s.osuser
2 from v$session s,v$process p
3 where s.paddr=p.addr;
------- ------- -------------- --------------------
9 253 SYSTEM oracle
10 20 SCOTT oracle
10 rows selected.
great the SID and SERIAL# that we need are 10 and 20.

A word about trace levels:

Before we use the DBMS_SYSTEM package to set trace in SCOTT's session we need to discuss what levels are. Trace in fact sets an event in the Oracle kernel (what is an event? - An event is simply a flag to the Oracle kernel to tell it to emit some trace messages or to add some additional processing or to activate some new functionality. Some events are used by support analysts and developers to force certain conditions to occur for testing purposes). In our case we want to look at event number 10046 - This event tells the Oracle kernel to emit trace lines and timings. The levels available in Oracle through some of the interfaces used to set trace are:
Level 0 = No statistics generated
Level 1 = standard trace output including parsing, executes and fetches plus more.
Level 2 = Same as level 1.
Level 4 = Same as level 1 but includes bind information
Level 8 = Same as level 1 but includes wait's information
Level 12 = Same as level 1 but includes binds and waits

For a complete list of events that can be set look at the file $ORACLE_HOME/rdmbs/mesg/oraus.msg on Unix or Linux. This file is not shipped on Windows systems. Also setting any event other that trace (10046) should not be done without the guidance of Oracle support.

Set trace in another session using DBMS_SYSTEM:

First lets set trace in SCOTT's session using the DBMS_SYSTEM package. Before we do let's turn on timed statistics so that the trace files get timing info and also set the dump file size so that there is plenty of room for the trace being generated.

SQL> exec dbms_system.set_bool_param_in_session(10,20,'timed_statistics',true);
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_int_param_in_session(10,20,'max_dump_file_size',2147483647);
PL/SQL procedure successfully completed.

OK, here we set trace in SCOTT's session

SQL> -- now use standard dbms_system interface
SQL> exec dbms_system.set_sql_trace_in_session(10,20,true);
PL/SQL procedure successfully completed.
SQL> -- execute some code
SQL> exec dbms_system.set_sql_trace_in_session(10,20,false);
PL/SQL procedure successfully completed.

A second way to set trace in another session - This time setting trace level as well
Next we can again use the DBMS_SYSTEM interface but this time use the set event syntax. This allows us to set any event in the database. This is of course not sanctioned by Oracle support and can cause damage to your database if not done correctly. Use this interface with care and just set 10046 (trace) events. Here is how it is done:

SQL> exec dbms_system.set_ev(10,20,10046,8,'');
PL/SQL procedure successfully completed.
SQL> -- execute some code
SQL> exec dbms_system.set_ev(10,20,10046,0,'');
PL/SQL procedure successfully completed.

Installing the DBMS_SUPPORT package:

Using the example above we set trace to level 8, you can of course set it to any level you wish from the list we discussed above. Next we will use the DBMS_SUPPORT package to set trace. This package is not installed by default and is in fact undocumented and indeed on some platforms and versions its not even shipped and you will need to talk to Oracle support and get it from metalink. First we will install the package:

SQL> -- now do the same with dbms_support
SQL> -- the package has to be installed first - you should ask Oracle first though!
SQL> @%ORACLE_HOME%\rdbms\admin\dbmssupp.sql
Package created.
Package body created.

Reviews and Comments

PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016

About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy