1. Query that will return the trace file for a given session or for all sessions attached to the database. You should be connected as SYSDBA
  2. Automation for Enabling and disabling SQL Trace

Example-1

Query that will return the trace file for a given session or for all sessions attached to the database. You should be connected as SYSDBA

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

Example-2

Automation for Enabling and disabling for SQL Trace. The query gets the SID and serial no for the session that need tracing.

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

The procedure enable tracing and disable automatically after time you specified. It would also give us tracing directory with file name. You have to specify time interval in seconds

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;
Go to top
JSN Boot template designed by JoomlaShine.com