/**********************************************************************
* Description:
* SQL*Plus script to start the SQL Trace for another session. This
* script displays the trace file name and location. This script will
* display the user sessions and prompt for information it needs.
*
*********************************************************************/
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
column in_db_namet new_value out_db_namet noprint
column in_spidt new_value out_spidt noprint
column in_usert new_value out_usert noprint
column in_patht new_value out_patht noprint
select value in_db_namet, user in_usert
from v$parameter
where name = ‘db_name’;
select value in_patht
from v$parameter
where name = ‘user_dump_dest’;
alter session set tracefile_identifier = &out_usert;
SET TERMOUT ON
column program format a20
select username, sid, serial#, program
from v$session
where program NOT LIKE ‘ORACLE%’;
ACCEPT in_user PROMPT ‘Enter User ID from list above : ‘
ACCEPT in_sid PROMPT ‘Enter SID from list above : ‘
ACCEPT in_serial PROMPT ‘Enter SERIAL from list above : ‘
PROMPT
PROMPT ‘Write the SID and SERIAL down…will need them to stop the trace’
PROMPT
SET TERMOUT OFF
select p.spid in_spidt
from v$process p, v$session s
where p.addr = s.paddr
and s.username = upper(‘&in_user’)
and s.sid = &in_sid;
SET TERMOUT ON
PROMPT
PROMPT Started SQL Trace in session
SELECT ‘&out_path’ “Trace File Path” from dual;
SELECT ‘&out_db_namet’ || ‘_ora_’ || ‘&out_spidt’ || ‘.trc’ “Trace File Name”
from dual;
PROMPT
rem exec dbms_system.set_ev(&in_sid, &in_serial, 10046, 12, ”);
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => &in_sid, serial_num => &in_serial, waits => TRUE, binds => TRUE);
SET FEEDBACK ON
SET VERIFY ON
SET ECHO ON