| How do i trace events in Oracle [message #39669] |
Thu, 01 August 2002 01:25  |
venkatesh
Messages: 72
Registered: August 2000
|
Member |
|
|
hi all
we have SQL Profiler for tracing events in MS SQL.
wht is there in Oracle to trace events in a session or in a database....?
with regards
venki
|
|
|
|
| Re: How do i trace events in Oracle [message #39680 is a reply to message #39669] |
Thu, 01 August 2002 16:34  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Sure - "alter session set timed_statistics=true" and "alter session set sql_trace=true" followed by "alter session set sql_trace=false" when you are done.
Put the code in a login trigger, enter it manually if you are in sqlplus (or stick it in login.sql) or you can imbed it in your code. To call it from pl/sql in 8i, use:
execute immediate 'alter session ...';
To run trace in a session which is already running:
-- Get SID, Serial#
SELECT sid, serial#, username, status, osuser, machine, terminal, module
FROM v$session
WHERE serial# != 1
ORDER BY 4;
-- enable trace in the other session (need DBA privs)
begin
sys.dbms_system.set_sql_trace_in_session( {sid}, {serial#}, TRUE );
end;
The trace file will be in the udump directory.
select name, value from v$parameter where name = 'user_dump_dest';
You can add the following to the init.ora to make accessing the files easier:
_trace_files_public = TRUE
Run the *.trc file through "tkprof" to format the output.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
