Start sqlplus session and set the tracefile_identifier
SQL> ALTER SESSION SET tracefile_identifier = JVM_TRC;
Use followwing query to get the correct SPID:
SQL> select s.username, p.spid, p.traceid  from v$session s, v$process p
        where s.paddr = p.addr and s.username ='SCOTT';

USERNAME                       SPID                     TRACEID
------------------------------ ------------------------ --------------------
SCOTT                          4032                     JVM_TRC
Run a long running query like:
SQL> select count(*) from all_objects a, all_objects b;
Use the SPID for Oradebug :
SQL> oradebug setospid 4032
Oracle pid: 21, Windows thread id: 4032, image: ORACLE.EXE (SHAD)
SQL> oradebug unlimit
SQL> oradebug dump errorstack 3
SQL> oradebug tracefile_name

View tracefile:

-----Current SQL Statementfor this session (sql_id=431997np5nt4p) -----
 select count(*) from all_objects a, all_objects b
 ----- Call Stack Trace -----
 calling              call     entry                argument values in hex
 location             type     point                (? means dubious value)
 -------------------- -------- -------------------- ----------------------------
 kghrcdepth()+177     VIRTUAL  0000000000000000     000000000 000000000 000000000
 kghrcappl()+92       CALL???  kghrcdepth()+37      000023678 012819B40
 7FF889A7620 012819B40
 kghfrempty_ex()+108  CALL???  kghrcappl()          000000000 0000248A8 000023680


Leave a Reply

Your email address will not be published. Required fields are marked *