Dear sir!
Following your book, Expert One by One, you guide:
<quote>
Today however, many applications are web¿based and this trick does not work as
well. The sessions are very short and they come and go frequently. What we need is an
ability to set SQL_TRACE on for a ¿user¿ ¿ whenever this user is in the database, we need
to have set SQL_TRACE on for them. Fortunately, we can do that via the LOGON DDL
Expert one-on-one Oracle
569
trigger in the database. For example, a trigger I frequently use in Oracle 8i (database event
triggers such AFTER LOGON are a new feature of Oracle 8.1.5 and up) is:
</quote>
Yes! I have to keep trace in my udump, then, I created:
<code>
sys@META> set feedback on
sys@META> CREATE OR REPLACE TRIGGER logon_trigger
2 AFTER
3 LOGON
4 ON DATABASE
5 BEGIN
6 IF (USER = 'HR')
7 THEN
8 EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEX
T FOREVER, LEVEL 12''';
9 END IF;
10 END;
11 /
Trigger created.
sys@META> show err
No errors.
sys@META>
sys@META> col object_name format a20
sys@META> col status format a15
sys@META> select object_name, status
2 from dba_objects
3 where owner='SYS'
4 and object_type='TRIGGER'
5 and object_name like 'LOGO%';
OBJECT_NAME STATUS
-------------------- ---------------
LOGON_TRIGGER VALID
sys@META>
What's the user I tracked? It's HR - in test environment. Now, whatever I want is the trace_file_name in my udump which was generated by HR's session. But, I can not, I will repeat steps following:
1/ Identified every trace file in udump
sys@META> @sqlplus\get_trace_file
FILE_TRACE
--------------------------------------------------------------------------------
--------------------
C:\ORACLE\PRODUCT\10.2.0\ADMIN\META\UDUMP\meta_ora_2872.trc 27-11-2008 14:03:29
C:\ORACLE\PRODUCT\10.2.0\ADMIN\META\UDUMP\meta_ora_632.trc 27-11-2008 14:03:29
2 rows selected.
FILE_TRACE
--------------------------------------------------------------------------------
--------------------
C:\ORACLE\PRODUCT\10.2.0\ADMIN\META\UDUMP\meta_ora_2872.trc 27-11-2008 14:03:29
C:\ORACLE\PRODUCT\10.2.0\ADMIN\META\UDUMP\meta_ora_632.trc 27-11-2008 14:03:29
2 rows selected.
FILE_TRACE
--------------------------------------------------------------------------------
--------------------
C:\ORACLE\PRODUCT\10.2.0\ADMIN\META\UDUMP\meta_ora_2872.trc
C:\ORACLE\PRODUCT\10.2.0\ADMIN\META\UDUMP\meta_ora_632.trc
2 rows selected.
sys@META>
<code>
2. Connect to HR, do some thing
<code>
sys@META> conn hr/hr
Connected.
hr@META> set feedback on
hr@META> select employee_id, last_name, first_name
2 from employees
3 /
EMPLOYEE_ID LAST_NAME FIRST_NAME
----------- ------------------------- --------------------
198 OConnell Donald
199 Grant Douglas
200 Whalen Jennifer
201 Hartstein Michael
202 Fay Pat
203 Mavris Susan
204 Baer Hermann
205 Higgins Shelley
206 Gietz William
100 King Steven
101 Kochhar Neena
102 De Haan Lex
103 Hunold Alexander
104 Ernst Bruce
105 Austin David
106 Pataballa Valli
107 Lorentz Diana
108 Greenberg Nancy
109 Faviet Daniel
110 Chen John
111 Sciarra Ismael
112 Urman Jose Manuel
113 Popp Luis
114 Raphaely Den
115 Khoo Alexander
116 Baida Shelli
117 Tobias Sigal
118 Himuro Guy
119 Colmenares Karen
120 Weiss Matthew
121 Fripp Adam
122 Kaufling Payam
123 Vollman Shanta
124 Mourgos Kevin
125 Nayer Julia
126 Mikkilineni Irene
127 Landry James
128 Markle Steven
129 Bissot Laura
130 Atkinson Mozhe
131 Marlow James
132 Olson TJ
133 Mallin Jason
134 Rogers Michael
135 Gee Ki
136 Philtanker Hazel
137 Ladwig Renske
138 Stiles Stephen
139 Seo John
140 Patel Joshua
141 Rajs Trenna
142 Davies Curtis
143 Matos Randall
144 Vargas Peter
145 Russell John
146 Partners Karen
147 Errazuriz Alberto
148 Cambrault Gerald
149 Zlotkey Eleni
150 Tucker Peter
151 Bernstein David
152 Hall Peter
153 Olsen Christopher
154 Cambrault Nanette
155 Tuvault Oliver
156 King Janette
157 Sully Patrick
158 McEwen Allan
159 Smith Lindsey
160 Doran Louise
161 Sewall Sarath
162 Vishney Clara
163 Greene Danielle
164 Marvins Mattea
165 Lee David
166 Ande Sundar
167 Banda Amit
168 Ozer Lisa
169 Bloom Harrison
170 Fox Tayler
171 Smith William
172 Bates Elizabeth
173 Kumar Sundita
174 Abel Ellen
175 Hutton Alyssa
176 Taylor Jonathon
177 Livingston Jack
178 Grant Kimberely
179 Johnson Charles
180 Taylor Winston
181 Fleaur Jean
182 Sullivan Martha
183 Geoni Girard
184 Sarchand Nandita
185 Bull Alexis
186 Dellinger Julia
187 Cabrio Anthony
188 Chung Kelly
189 Dilly Jennifer
190 Gates Timothy
191 Perkins Randall
192 Bell Sarah
193 Everett Britney
194 McCain Samuel
195 Jones Vance
196 Walsh Alana
197 Feeney Kevin
107 rows selected.
hr@META> host sqlplus /"as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 27 14:06:40 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@META> @sqlplus\get_trace_file
sys@META> @sqlplus\get_trace_file
FILE_TRACE
-----------------------------------------------------------
--------------------
C:\ORACLE\PRODUCT\10.2.0\ADMIN\META\UDUMP\meta_ora_3068.trc
C:\ORACLE\PRODUCT\10.2.0\ADMIN\META\UDUMP\meta_ora_632.trc
Nothing occurs in udump.
May you clarify me more?
P/S: I've set system sql_trace='context forever, level 12' , and I tried to use DBMS_SYSTEM.set_sql_trace_in_session, but not successfully.
sys@META> select sid, serial#, username
2 from v$session
3 where username='HR';
SID SERIAL# USERNAME
---------- ---------- ------------------------------
142 69 HR
sys@META> execute start_trace(142,69,30)
Tracing Started for User: HR
Tracing Start Time: 11-27-2008 14:12:51
Tracing Stop Time: 11-27-2008 14:13:21
Trace Directory: C:\ORACLE\PRODUCT\10.2.0\ADMIN\META\UDUMP
Trace Filename:
sys@META> alter session set events '10046 trace name context forever, level 12';
sys@META> select sid, serial#, username
2 from v$session
3 where username='HR';
SID SERIAL# USERNAME
---------- ---------- ------------------------------
142 69 HR
sys@META> execute start_trace(142,69,30)
Tracing Started for User: HR
Tracing Start Time: 11-27-2008 14:14:13
Tracing Stop Time: 11-27-2008 14:14:43
Trace Directory: C:\ORACLE\PRODUCT\10.2.0\ADMIN\META\UDUMP
Trace Filename:
sys@META>
No trace file for HR! I will show you the store procedure START_TRACE
CREATE OR REPLACE
PROCEDURE start_trace (
v_sid IN NUMBER,
v_serial# IN NUMBER,
seconds IN NUMBER
)
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')
);
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'
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;
/
</code>