Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

No trace file generated when I cannot thru a listener

Laurent SchneiderNov 27 2008 — edited Nov 28 2008
I am surely missing something trivial, thanks if you find it before me :)

I am trying to generate trace files with ALTER SESSION SET SQL_TRACE=TRUE. It works with a local connection, but not when connected thru a listener.

Any clue?
$ sqlplus scott/tiger

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Nov 27 14:12:19 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> select spid from v$process where addr=(select paddr from v$session where sid in (select sid from v$mystat));
SPID
------------
29334

SQL> alter session set sql_trace=true;

Session altered.

SQL> select sysdate from dual;
SYSDATE
---------
27-NOV-08

SQL> alter session set sql_trace=false;

Session altered.

SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
$ cd /app/oracle/admin/LSC01/udump 
$ ls *29334*
lsc01_ora_29334.trc
$ sqlplus scott/tiger@lsc01

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Nov 27 14:14:42 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> select spid from v$process where addr=(select paddr from v$session where sid in (select sid from v$mystat));
SPID
------------
11395

SQL> alter session set sql_trace=true;

Session altered.

SQL> select sysdate from dual;
SYSDATE
---------
27-NOV-08

SQL> alter session set sql_trace=false;

Session altered.

SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
$ cd /app/oracle/admin/LSC01/udump 
$ ls *11395*
*11395*: No such file or directory
This post has been answered by 153119 on Nov 27 2008
Jump to Answer

Comments

153119
Answer
shared server, by any chance?
In that case it won't work and your session will end up in various s<nnnn> traces.

--
Sybrand Bakker
Senior Oracle DBA
Marked as Answer by Laurent Schneider · Sep 27 2020
Laurent Schneider
Excellent, I did not know this...

For completion, here is the test case, it fully solved my issue as I am now forcing to use dedicated server before connect to get a full trace.
$ sqlplus "scott/tiger@
  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.laurentschneider.com)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=LSC01.laurentschneider.com)(SERVER=SHARED)))"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 28 16:14:18 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> select s.sid,ss.name, p.spid
  2  from v$shared_server ss
  3  right join v$circuit c using (circuit)
  4  right join v$session s on ( s.saddr=c.saddr )
  5  join v$process p on (s.paddr=p.addr)
  6  where sid in (
  7      select sid from v$mystat);
       SID NAME SPID
---------- ---- ------------
       323 S001 11397

SQL> alter session set sql_trace=true;

Session altered.

SQL> select sysdate from dual;
SYSDATE
-------------------
2008-11-28 16:30:21

SQL> alter session set sql_trace=false;

Session altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

$ ls *11397*
*11397*: No such file or directory
$ sqlplus "scott/tiger@
  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.laurentschneider.com)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=LSC01.laurentschneider.com)(SERVER=DEDICATED)))"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 28 16:30:37 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL>
SQL> select s.sid,ss.name, p.spid
  2  from v$shared_server ss
  3  right join v$circuit c using (circuit)
  4  right join v$session s on ( s.saddr=c.saddr )
  5  join v$process p on (s.paddr=p.addr)
  6  where sid in (
  7      select sid from v$mystat);
       SID NAME SPID
---------- ---- ------------
       323      10965

SQL> alter session set sql_trace=true;

Session altered.

SQL> select sysdate from dual;
SYSDATE
-------------------
2008-11-28 16:31:16

SQL> alter session set sql_trace=false;

Session altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
$ ls *10965*
LSC01_ora_10965.trc
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 26 2008
Added on Nov 27 2008
#general-database-discussions
2 comments
757 views