| Enabling trace for single user [message #52254] |
Thu, 11 July 2002 01:34  |
rajesh
Messages: 173
Registered: November 1998
|
Senior Member |
|
|
I want to know how is it possible to put trace on one single user from server.
What is the syntax? What are the prerequisites?
|
|
|
|
| Re: Enabling trace for single user [message #52263 is a reply to message #52254] |
Thu, 11 July 2002 09:17  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
-- run from a DBA account:
SQL> descr sys.dbms_system
...
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
...
get SID and SERIAL# from v$session for the session you are interested in.
SQL> exec sys.dbms_system.set_sql_trace_in_session( 46, 13217, TRUE );
If you are doing it for tuning - enable timed statistics if it's not already enabled in init.ora.
-- Warning - if this trigger fails - login fails (except for an account having DBA role)
-- this exact code not tested.
create or replace trigger login_trigger
after logon on schema
begin
if USER = 'SCOTT' then
execute immediate
'ALTER SESSION SET TIMED_STATISTICS=TRUE';
execute immediate
'ALTER SESSION SET SQL_TRACE=TRUE;
end if;
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
