1

I was working with sql server databases for years and I am used to tools like sql server profiler for tracing all queries on particular database.

My question is - how to do this same thing with oracle database. I am using sql oracle developer version 19.2 and I don't see any option there for tracing DB.

I've also checked this question and tried several tools there (the question is quite old) with no success. Do I really need oracle enterprise manager for doing such a basic thing? (that is suggestion from one of the answers).

My application is written in .NET - why is it so hard in oracle to track queries that this app is triggering?

Improve this question
2
  • 1
    You can use monitor sessions in Oracle Sql Developer Tools-->Monitor Sessions
    – user168186
    Mar 14, 2020 at 1:25
  • @Kumar - this is only if you want to see sessions, not for sql querying in real time. But, sql developer also has sql querying in real time, but it's separated license.
    – FrenkyB
    Mar 15, 2020 at 6:26

2 Answers 2

Reset to default
1

alter session set sql_trace=true is enough to get only SQL query text for the current session.

Export/import to a local system is also OK to get only SQL query text.

Improve this answer
0
2

Tracing is difficult in Oracle Database for several reasons:

  1. you need to identify the involved database sessions which is more or less easy
  2. you need to use DBMS_MONITOR package (only granted to SYSDBA by default)
  3. you need to have access to the raw trace files (they are generated on database server side - see V$DIAG_INFO view)
  4. you need to use TKPROF on the raw trace file to have something readable (TKPROF is part of Oracle Client but not Oracle Instant Client).

If you need to have only SQL statements without execution plan and detailed timings you could modify your code to enable SQL trace with "ALTER SESSION SET SQL_TRACE=TRUE" (raw trace file generated on db server side).

If you have your dedicated database instance (like Oracle XE) you could just enable SQL trace on database level (ALTER SYSTEM SET SQL_TRACE=TRUE) and get all trace files.

Here is one of the best summary of SQL tracing options in Oracle: https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

You don't need OEM to enable trace but you need DBA privileges or to work with your DBA to use SQL trace.

Improve this answer
2
  • Thanks for answer, it clarifies a lot of things for me. Basically what I need is just information about what query was triggered, nothing else. I don't need execution plans and other things. Just what query (or procedure) was triggered.
    – FrenkyB
    Mar 13, 2020 at 17:26
  • What do you mean if I simply export database and import it in my local system - I guess tracing is then easier, since I will be DBA.
    – FrenkyB
    Mar 13, 2020 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.