Oracle SQL Tracing on other sessions

Question

I am studying Oracle database. I have a question. That is tracing SQL on other sessions.

Here is my work:

SYS>

-- Get sid and serial of session which I'm gonna analyze
select sid, serial# from v$session where username = 'DEV';


-- Activate SQL Trace on the session(sid:69 / serial:72)
begin
dbms_monitor.session_trace_enable(69, 72);
end;
/

-- Check sql_trace is set 'ENABLED'
select sql_trace from v$session where username = 'DEV';


DEV>

-- SQL to be traced
select 'a' from dual connect by level <= 10;


SYS>

-- Deactivate SQL Trace

begin
  dbms_monitor.session_trace_disable(69, 72);
end;
/

-- .trc file location check
select p.tracefile from v$process p, v$session s where p.addr = s.paddr and s.sid = userenv('sid');

And after that work, I tried to find the *.trc filie, but it doesn't exist. Whereas, when I try to trace the session which calls SQL Trace itself, I can find trc file(works fine).

What could be the reason not to be able to trace on other sessions?

Thanks

atokpas · Accepted Answer · 2016-09-21T03:30:12.160

Here is the way to enable trace in another session.

SQL> conn test/test;
Connected.

SYS user:

SQL> conn / as sysdba
SQL> select sid, serial# from v$session where username='TEST';

SID SERIAL#
------- -------------
28  27

SQL> exec sys.dbms_system.set_sql_trace_in_session(28, 27, TRUE);

TEST user:

SQL> select table_name from tabs;

TABLE_NAME
------------------------------
TABLES_PKEYS

SQL> select * from tables_pkeys;

SCHEMA_NAME      TABLE_NAME       COLUMN_NAME          PKEY_INDEX
-------------------- -------------------- -------------------- ----------
DEMO             T1           ID                1

SQL>

SYS user:

SQL> select p.tracefile from v$process p, v$session s where p.addr=s.paddr and s.sid='28';

TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4122.trc

SQL>exit

[oracle@testsrv trace]$ cat orcl_ora_4122.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4122.trc
 ...

 *** 2016-09-18 21:04:09.013
 *** SESSION ID:(28.27) 2016-09-18 21:04:09.013
 *** CLIENT ID:() 2016-09-18 21:04:09.013
 *** SERVICE NAME:(SYS$USERS) 2016-09-18 21:04:09.013
 *** MODULE NAME:(SQL*Plus) 2016-09-18 21:04:09.013
 *** ACTION NAME:() 2016-09-18 21:04:09.013

   CLOSE #139829905643504:c=1000,e=327,dep=0,type=0,tim=1474212849012528
    =====================
    PARSING IN CURSOR #139829906751864 len=202 dep=1 uid=0 oct=3 lid=0       tim=1474212849015309 hv=3819099649 ad='67af7630' sqlid='3nkd3g3ju5ph1'

You can use TKPROF, an Oracle supplied tool to view nice and formatted trace contents.

Oracle SQL Tracing on other sessions

1 Answers1