sql_trace

exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
exec dbms_system.set_sql_trace_in_session(38,3,true);
exec dbms_system.set_sql_trace_in_session(38,3,false);




tkprof C:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_224_tuncay_sql_trace.trc C:\sql_trace.txt explain=esastest/esastest sys=yes waits= yes

tkprof C:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_224_tunca_sql_trace.trc C:\toutput.txt insert=C:\tkprof.sql record=C:\allsql.sql


alter session set sql_trace =true

alter session set tracefile_identifier = 'tuncay_sql_trace';





----------------
select b.username, c.value || '\' || lower(d.value) || '_ora_' ||
to_char(a.spid, 'fm00000') || '.trc' "TRACE_FILE",
a.pid PID, b.serial# session_number
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;


/* Formatted on 2008/06/05 14:49 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE start_trace (
v_sid IN NUMBER,
v_serial# IN NUMBER,
seconds IN NUMBER
)
---------------------------------------------

-- 2003 - Oracle Utilities

-- D. Moore

--

-- 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'
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;


---------------------
exec start_trace(17, 6157, 30);