start trace without alter session

[danmul]

Hi,

Is it possible to start a trace without having the alter session privilege?
We have a user that we have set-up to monitor DB activities for all users but we dont want to grant alter session privilege to it but we do want to allow the user to start/stop traces.
We granted DBMS_MONITOR and they can execute the package but it does not start the trace. From reading I believe this i sbecause the user needs the alter session also?

Anyone know of any other ways to start a trace or if there is a way around this.
Thanks,
Daniel.

 

[karluk]

I tested this by granting a test user execute on dbms_monitor but not granting the alter session privilege. My test id had no trouble at all starting and stopping session traces with dbms_monitor.session_trace_enable and dbms_monitor.session_trace_disable. I would suspect that you provided an incorrect session id or serial number as input to dbms_monitor, but then you should have gotten an "ORA-00030: User session ID does not exist."

Could you elaborate a little more on the command you actually used to try to start the trace and what, if any, error messages you got?

 

[danmul]

Hi Karluk,

This is the comamnd I used: EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(527,2, TRUE, TRUE);
The command executed(Returning PL/SQL procedure successfully completed) so I was expecting the trace to start but there was no file produced. I checked DBA_ENABLED_TRACES but there was no entry for it. I have tried it with a few times with different session, serial# with the same results.
Is there something else that I am missing.

Cheers,
Daniel.

 

[karluk]

It's not surprising that you didn't find anything in DBA_ENABLED_TRACES - session traces don't persist across database shutdowns and aren't recorded in DBA_ENABLED_TRACES - but it certainly is puzzling that you don't see any trace file output.

Here is what I did to enable tracing. I assume you did something quite similar.

First I identified the session id and serial number of the session I wanted to trace.

select sid, serial# from v$session where ...
       SID    SERIAL#
---------- ----------
       124      44113

Then I enabled tracing using dbms_monitor.

execute dbms_monitor.session_trace_enable(124, 44113, true, true);

PL/SQL procedure successfully completed.

I then ran some queries in the session being monitored, turned off tracing and identified the udump directory.

execute dbms_monitor.session_trace_disable(124, 44113);

PL/SQL procedure successfully completed.
show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /DEV/u03d/admin/od003/udump

I then logged into my database server and found a newly created file in /DEV/u03d/admin/od003/udump named od003_ora_7765.trc. I ran the tkprof utility on this file

tkprof od003_ora_7765.trc session_trace.rpt

and got a report in the output file. The first few lines were

TKPROF: Release 10.2.0.4.0 - Production on Thu Mar 4 10:35:39 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: od003_ora_7765.trc
Sort options: default

 

[danmul]

Hi,

I installed XE this morn and did this on my PC to test and it works on my PC.
Here’s what I did:
Logged in as system:
CREATE USER dan IDENTIFIED BY dan DEFAULT TABLESPACE USERS;
grant connect, resource to dan;
Logged in as sys:
GRANT EXECUTE ON dbms_monitor to dan;
Logged in as dan:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(25,36, TRUE, TRUE);

Im not sure what is wrong with our test system. There must be some privilege somewhere blocking this. Back to the books for me. I'll keep trying and if you have any other ideas let me know.

Thanks again,
Daniel.