1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

COLUMN username format a10
COLUMN trace_file format a70
 
SELECT b.username, 
          c.VALUE 
       || '\' 
       || LOWER (d.VALUE) 
       || '_ora_' 
       || TO_CHAR (a.spid, 'fm000000') 
       || '.trc' "TRACE_FILE" 
  FROM v$process a, v$session b, v$parameter c, v$parameter d
  WHERE a.addr = b.paddr
   AND c.NAME = 'user_dump_dest' 
   AND d.NAME = 'db_name' 
   AND b.username IS NOT NULL 
   AND b.username NOT IN ('DBSNMP', 'SYSMAN');

1
2
3
4
5
6
7

SELECT SID, serial#, username
  FROM v$session
  WHERE username NOT IN ('DBSNP', 'SYSMAN', 'SYS', 'SYSTEM');
 
       SID    SERIAL# USERNAME
---------- ---------- ----------
       145      27032 SCOTT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

SET serveroutput ON
 
EXECUTE start_trace(145,27032,60); 
Tracing Started FOR User: SCOTT
Tracing Start Time: 02-20-2009 12:11:43
Tracing Stop Time: 02-20-2009 12:12:45
Trace Directory: C:\ORACLE\PRODUCT\10.2.0\ADMIN\DEVDB\UDUMP
Trace Filename: DEVDB_ora_125140.trc
 
 
SQL_TRACE procedure code
 
CREATE OR REPLACE PROCEDURE start_trace ( 
   v_sid       IN   NUMBER, 
   v_serial#   IN   NUMBER, 
   seconds     IN   NUMBER 
) 
 
--------------------------------------------- 
-- 2003 - Oracle Utilities 
-- This procedure serves as a wrapper to session tracing. 
-- It accepts a sid and serial#, along with the amount of 
-- time in seconds that the trace should last. 
-- The trace will be stopped when that time 
-- period expires. After tracing is turned 
-- off, the name of the trace file will be 
-- displayed. 
--------------------------------------------- 
 
IS 
   v_user             VARCHAR2 (32); 
   stop_trace_cmd     VARCHAR2 (200); 
   DURATION           NUMBER; 
   v_spid             NUMBER; 
   dump_dest          VARCHAR2 (200); 
   db_name            VARCHAR2 (32); 
   v_version          VARCHAR2 (32); 
   v_compatible       VARCHAR2 (32); 
   file_name          VARCHAR2 (32); 
   no_session_found   EXCEPTION; 
 
BEGIN 
   BEGIN 
      SELECT a.username, b.spid
        INTO v_user, v_spid
        FROM v$session a, v$process b
       WHERE a.SID = v_sid AND a.serial# = v_serial# AND a.paddr = b.addr; 
 
   EXCEPTION 
      WHEN NO_DATA_FOUND 
      THEN 
         RAISE no_session_found; 
   END; 
 
   DBMS_SYSTEM.set_sql_trace_in_session (v_sid, v_serial#, TRUE); 
   DBMS_OUTPUT.put_line ('Tracing Started for User: ' || v_user); 
   DBMS_OUTPUT.put_line (   'Tracing Start Time: ' 
                         || TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') 
                        ); 
 
--------------------------------------------------- 
-- Sleep for the amount of seconds specified as 
-- seconds input parameter. When complete, stop 
-- the tracing and display the resulting trace file 
-- name 
--------------------------------------------------- 
 
   IF seconds IS NULL 
   THEN 
      DURATION := 60; 
   ELSE 
      DURATION := seconds; 
   END IF; 
 
   DBMS_LOCK.sleep (DURATION); 
 
-- the time alotted has now expired. Disable 
-- tracing and output the trace file information 
   DBMS_SYSTEM.set_sql_trace_in_session (v_sid, v_serial#, FALSE); 
   DBMS_OUTPUT.put_line (   'Tracing Stop Time: ' 
                         || TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') 
                        ); 
 
-- get all of the data needed to format the trace file name 
   SELECT VALUE 
     INTO dump_dest
     FROM v$parameter
    WHERE NAME = 'user_dump_dest'; 
 
   SELECT VALUE 
     INTO db_name
     FROM v$parameter
    WHERE NAME = 'db_name'; 
 
-- we need the version of the database in order to determine 
-- the naming scheme for the trace file 
   DBMS_UTILITY.db_version (v_version, v_compatible); 
 
   IF SUBSTR (v_version, 1, 1) = '9' OR SUBSTR (v_version, 1, 2) = '10'  
   THEN 
      file_name := db_name || '_ora_' || v_spid || '.trc'; 
 
   ELSIF SUBSTR (v_version, 1, 3) = '8.1' 
   THEN 
      file_name := 'ora_' || v_spid || '_' || db_name || '.trc'; 
   ELSIF SUBSTR (v_version, 1, 3) = '8.0' 
 
   THEN 
      file_name := 'ora_' || v_spid || '.trc'; 
 
   END IF; 
 
   DBMS_OUTPUT.put_line ('Trace Directory: ' || dump_dest); 
   DBMS_OUTPUT.put_line ('Trace Filename: ' || file_name); 
 
EXCEPTION 
   WHEN no_session_found
   THEN 
      DBMS_OUTPUT.put_line ('No session found for sid and serial# 
specified'); 
 
END start_trace;