Quickly locate trc files generated by other sessions

To quickly find the trc files generated by tracking other sessions, the most fundamental technology is to understand the path where the session tracking files are stored and the naming rules for generating the tracking files. Otherwise, in a production environment where tens of thousands of trc files already exist, you need to quickly and correctly find the trc files generated by tracking other sessions, just like a haystack. Tracking other sessions is generally performed based on sid + serial #, but the name generated by the trc file has no relationship with SID and Serial #, but it is only related to the root SPID. Therefore, it is very important to understand the path where the session tracking file is stored and the naming rules for generating the tracking file. The following describes how to create a session tracking file. 1. The path where the SESSION tracking file is stored is determined by the user_dump_dest parameter of the database. For example:

SQL> show parameter user_dump_destNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------user_dump_dest                       string      /dba/oracle/diag/rdbms/litest/litest/trace

 

2. The naming rule "Instance_name" + "_ ora _" + "spid. trc "For example: litest_ora_12262.trc path name + trace file name example:/dba/oracle/diag/rdbms/litest/trace/litest_ora_12262.trc 3, 10046 trace other SID sessions, find the trc file instance. (1) query the SID or SPID of the target session. A. Check the spid of the target session according to its SID.

SQL> select p.spid,s.sid,s.serial# from v$session s,v$process p where s.paddr=p.addr and s.sid=38;SPID                                  SID           SERIAL#------------------------         ----------    ----------16334                                  38            1499

 

B. Alternatively, locate the sid and serial Based on the SPID of the target session #

SQL> select s.sid,s.serial#,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=16334;       SID                 SERIAL#      SPID---------- ---------- -----------      -------------        38                       1499       16334

 

From the above query, we have found that the SPID of SID 38 and Serail #1499 is 16334 (2) 10046 tracking target SESSION (SID = 38, SERIAL # = 1499)

SQL> execute dbms_system.set_ev(38,1499,10046,12,'');PL/SQL procedure successfully completed.

 

(3) After the target session generates activity information, search for the trc file in the udump directory based on the SPID 16334 of the target session.

 [oracle@oraclelinux trace]$ pwd/dba/oracle/diag/rdbms/litest/litest/trace[oracle@oraclelinux trace]$ ls -l |grep 16334-rw-r-----. 1 oracle oinstall   4703 Aug 15 13:42 litest_ora_16334.trc-rw-r-----. 1 oracle oinstall    168 Aug 15 13:42 litest_ora_16334.trm

 

Now, the trc file litest_ora_16334.trc with the 10046 trail SID 38 has been found in the udump directory. The absolute path of the file + file name is: /dba/oracle/diag/rdbms/litest/trace/litest_ora_16334.trc (4) do not forget to stop tracking SID 38.

SQL> execute dbms_system.set_ev(38,1499,10046,0,'');PL/SQL procedure successfully completed.