How to set trace for others sessions, for your own session and at instance level

a very interesting article which I found at src

This short paper is not a polished white paper (so please do not expect lots of explanatory text) but is intended to be a good example to show the many ways to set Oracle trace in your own session or in another session. let's get on with it:

Tools to analyse trace files

Up to and including Oracle 10g the tool that is generally used to analyse trace files is called tkprof. This tool formats the trace files that have been generated into a more readable format. Understanding the trace file format seems daunting on first inspection. A good source for details on the trace file format is a metalink note 39817.1. In 10g there is a new tool for formatting trace files called trcsess. This tool has been designed to deal with the new trace facilities that allow trace to be identified based on client identifier or by a combination of service name / module / action. This allows trace to be completed even if connection pooling and multi-threading is used. An individual client in these circumstances could share many different sessions.

Find out where the trace file will be written to

If the user you are using is not a DBA or to be more specific has not been granted access to the data dictionary view V$PARAMETER then you will need to use this technique to find out where your trace files are written to:

    SQL> set serveroutput on size 1000000 for wra
    SQL> declare
      2  paramname varchar2(256);
      3  integerval binary_integer;