OraFAQ Forum: Performance Tuning » how to enable trace for other sessions?

Home » RDBMS Server » Performance Tuning » how to enable trace for other sessions?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to enable trace for other sessions? [message #431184]

Mon, 16 November 2009 01:30

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?

Report message to a moderator

Re: how to enable trace for other sessions? [message #431189 is a reply to message #431184]

Mon, 16 November 2009 01:50

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.

Report message to a moderator

Re: how to enable trace for other sessions? [message #431190 is a reply to message #431184]

Mon, 16 November 2009 01:55

DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai

Member

rectifying the above syntax...

ALTER SYSTEM SET SQL_TRACE=TRUE;

Report message to a moderator

Re: how to enable trace for other sessions? [message #431192 is a reply to message #431189]

Mon, 16 November 2009 02:12

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

Report message to a moderator

Re: how to enable trace for other sessions? [message #431195 is a reply to message #431184]

Mon, 16 November 2009 02:26

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

Re: how to enable trace for other sessions? [message #431229 is a reply to message #431198]

Mon, 16 November 2009 04:24

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: how to enable trace for other sessions? [message #431969 is a reply to message #431184]

Fri, 20 November 2009 04:47

trantuananh24hg
Messages: 744
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

Report message to a moderator

Previous Topic:	query delay
Next Topic:	How to tune a update statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 18 22:54:36 CST 2025