Hi,
I am trying to trace the session to get SQL's executed by the session and i am using
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>3002, serial#=>31833,sql_trace=> true);
to start the trace.
When i use this package on a user with DBA priviledge then i am able to do it.
Method i use here is --
To get SID and SERIAL# of session --
SELECT sid, serial# FROM SYS.V_$SESSION
WHERE SID = (SELECT DISTINCT SID FROM SYS.V_$MYSTAT);
Then
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>3002, serial#=>31833,sql_trace=> true);
So my tracing start with this and i can the name and path of the trace file created for this using below query--
SELECT s.sid,
s.serial#,
pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
But when i try to use above method on a non-dba user then i am not able to check the trace_file name whereas i know the location where the file created.
So for a non-dba user i am using following method --
GRANT SELECT ON SYS.V_$SESSION TO USER;
GRANT SELECT ON SYS.V_$MYSTAT TO USER;
and then to get SID and SERIAL# --
SELECT sid, serial# FROM SYS.V_$SESSION
WHERE SID = (SELECT DISTINCT SID FROM SYS.V_$MYSTAT);
So now when i EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>3098, serial#=>31543,sql_trace=> true);
Then it gives me error ----
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
So i use below ALTER statement --
alter session set sql_trace = true;
OR
EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
But when i try to get the name of the trace file created for this then i am not able to this as i can not GRANT SELECT on the V$SESSION and V$PARAMETER view's
And one more thing to Ask is that can i define my OWN directory to store trace file which i have created within
app/oracle11g/diag/rdbms/ ?
I am Using DB version --
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Any suggestion !