Home » RDBMS Server » Server Administration » Tracing
| Tracing [message #371469] |
Wed, 25 October 2000 10:56  |
Julian Denbury
Messages: 1
Registered: October 2000
|
Junior Member |
|
|
I am currently using an application called Maximo which sits on Windows Terminal Server and I am trying to trace what happens when a user saves a record in the front end.
What is the SQL comand to do this
|
|
|
|
| Re: Tracing [message #371470 is a reply to message #371469] |
Wed, 25 October 2000 12:22  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
On your unix side you can try the following two from SQL*Plus:
1.) Run the following query to get the SID
select si.sid,
s.username "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
s.program "Program",
s.machine "Machine",
s.logon_time "Connect Time",
p.program "P Program",
s.process "Process",
p.spid, p.pid, s.serial#
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
2.) Run the trace.
rem #####################################################################
rem
rem Name: trace.sql
rem Author: Bernard van Aalst
rem Date: 24 February 2000
rem Purpose: Run SQL Trace on another session, and view the TKPROF output.
rem Usage: @trace <SID>
rem
rem Required privs: SELECT on v$session, v$parameter, v$process
rem EXECUTE on dbms_system
rem
rem #####################################################################
WHENEVER SQLERROR EXIT ROLLBACK
SET FEED OFF VERIFY OFF
DECLARE
CURSOR c IS SELECT NULL FROM v$session WHERE sid = &1;
dummy CHAR(1);
BEGIN
OPEN c;
FETCH c INTO dummy;
IF c%NOTFOUND
THEN
CLOSE c;
RAISE_APPLICATION_ERROR(-20001,'There is no session with sid = &1');
END IF;
CLOSE c;
END;
/
COL serial# NEW_VALUE _serial NOPRINT
COL username NEW_VALUE _user NOPRINT
COL spid NEW_VALUE _spid NOPRINT
SELECT s.username
, s.serial#
, p.spid
FROM v$session s
, v$process p
WHERE s.sid = &1
AND s.paddr = p.addr
/
COL value NEW_VALUE _udump NOPRINT
SELECT value
FROM v$parameter
WHERE name = 'user_dump_dest'
/
begin sys.dbms_system.set_sql_trace_in_session( &1, &_serial, true); end;
/
PROMPT Tracing is switched on... Any key to stop it and run tkprof.
PAUSE
begin sys.dbms_system.set_sql_trace_in_session( &1, &_serial, false); end;
/
PROMPT Tracing is switched off.
ACC do_explain DEF "Y" PROMPT "Do you want to use explain plan? (Y|N) Y "
DEFINE _user=""
DEFINE _pw=""
DEFINE _pw_question="Just enter..."
COL username NEW_VALUE _user NOPRINT
COL pw_question NEW_VALUE _pw_question NOPRINT
COL explain_section NEW_VALUE _exp NOPRINT
SELECT
DECODE
(s.username
,USER,DECODE(d.password
,'EXTERNAL','Just enter...'
, 'Enter your password:'
)
, DECODE(d.password
,'EXTERNAL','User is identified externally. Just enter...'
, 'Enter the password of '||s.username||':'
)
) pw_question
, DECODE
(s.username
,USER,DECODE(d.password
,'EXTERNAL','explain=/'
, 'explain='||s.username||'/'
)
, DECODE(d.password
,'EXTERNAL','' /* We cannot use explain plan */
, 'explain='||s.username||'/'
)
) explain_section
FROM v$session s
, dba_users d
WHERE s.sid = &1
AND s.username = d.username
AND UPPER('&do_explain') = 'Y'
/
ACC pw PROMPT "&_pw_question" HIDE
COL explain_section NEW_VALUE _exp NOPRINT
SELECT '&_exp'||'&pw' explain_section
FROM dual
WHERE LENGTH('&_exp') > 9
/
!tkprof &_udump/ora_&_spid..trc /tmp/trace.txt &_exp sort=\(prsela,exeela,fchela\)
PROMPT Any key to view the outputfile
PAUSE
ed /tmp/trace.txt
PROMPT tracefile: &_udump/ora_&_spid..trc
PROMPT outputfile: /tmp/trace.txt
SET FEED ON
CL COL
UNDEFINE _exp
UNDEFINE _pw_question
UNDEFINE _serial
UNDEFINE _spid
UNDEFINE _udump
UNDEFINE _user
UNDEFINE do_explain
UNDEFINE pw
WHENEVER SQLERROR CONTINUE
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 23:28:28 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
