Home » RDBMS Server » Performance Tuning » how to enable trace for other sessions?
how to enable trace for other sessions? [message #431184] Mon, 16 November 2009 01:30 Go to next message
priyasinha396
Messages: 15
Registered: November 2009
Location: Delhi
Junior Member
I found sid of my current session (user-scott)as,
select sys_context('USERENV','sid') from dual;
& found it to be 146
i am trying to execute as user 'system' to enable trace for scott as:

execute dbms_system.set_sql_trace_in_session (146,1327,true);

PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared

can anyone please rectify the error?
Re: how to enable trace for other sessions? [message #431189 is a reply to message #431184] Mon, 16 November 2009 01:50 Go to previous messageGo to next message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
Do ALTER SYSTEM SQL_TRACE=TRUE;
if not working...then

Try This ...
shutdown db.
Define SQL_TRACE=TRUE in your init.ora file.
Start the database.

revert me if it will work.
Re: how to enable trace for other sessions? [message #431190 is a reply to message #431184] Mon, 16 November 2009 01:55 Go to previous messageGo to next message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
rectifying the above syntax...

ALTER SYSTEM SET SQL_TRACE=TRUE;
Re: how to enable trace for other sessions? [message #431192 is a reply to message #431189] Mon, 16 November 2009 02:12 Go to previous messageGo to next message
priyasinha396
Messages: 15
Registered: November 2009
Location: Delhi
Junior Member
is the first syntax meant to enable the trace for all sessions?

I executed:
SQL> alter system set sql_trace = true;

System altered.

but..
select sql_trace from v$session is showing all the sessions disabled..

the second option is not working for me(shutdown)-- showing insufficient privileges. i just have two accounts : system & scott.

thanks,
Priya
Re: how to enable trace for other sessions? [message #431195 is a reply to message #431184] Mon, 16 November 2009 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to trace your own session just use:
alter session set sql_trace=true;

If you want to activate trace in other sessions, the best way depends on the version you didn't post.

Regards
Michel

[Updated on: Mon, 16 November 2009 02:27]

Report message to a moderator

Re: how to enable trace for other sessions? [message #431198 is a reply to message #431195] Mon, 16 November 2009 03:14 Go to previous messageGo to next message
priyasinha396
Messages: 15
Registered: November 2009
Location: Delhi
Junior Member
version is oracle 10g (Oracle Database 10g Release 10.2.0.3.0 - Production)
when logged in as 'scott' user:
SQL> alter session
2 set sql_trace = true;
alter session
*
ERROR at line 1:
ORA-01031: insufficient privileges

when logged in as'system' user,the above query works but
select sql_trace from v$session shows 'disabled' for every row

Re: how to enable trace for other sessions? [message #431229 is a reply to message #431198] Mon, 16 November 2009 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section. Use code tags.

Quote:
ORA-01031: insufficient privileges

Your user has not the privilege ALTER SESSION.

Quote:
when logged in as'system' user,the above query works but
select sql_trace from v$session shows 'disabled' for every row

This is because ALTER SESSION does not set this parameter, this does not mean that the trace is not activated.

SQL> select sql_trace from v$session where sid=sys_context('userenv','sid');
SQL_TRAC
--------
DISABLED

1 row selected.

SQL> alter session set sql_trace=true;

Session altered.

SQL> select sql_trace from v$session where sid=sys_context('userenv','sid');
SQL_TRAC
--------
DISABLED

1 row selected.

SQL> exec dbms_monitor.SESSION_TRACE_ENABLE;

PL/SQL procedure successfully completed.

SQL> select sql_trace from v$session where sid=sys_context('userenv','sid');
SQL_TRAC
--------
ENABLED

1 row selected.

Regards
Michel
Re: how to enable trace for other sessions? [message #431969 is a reply to message #431184] Fri, 20 November 2009 04:47 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
According to M.Caldot, I suggest you post your 4 decimals Oracle version.
If you are using 10g, you'd rather think of DBMS_MONITOR
Previous Topic: query delay
Next Topic: How to tune a update statement
Goto Forum:
  


Current Time: Sat Dec 03 19:53:08 CST 2016

Total time taken to generate the page: 0.09222 seconds