Loggin of SQL queries for Oracle 12c and Oracle 10.2g, help!

Hi guys, this is an extension to another question which is still being answered, and which led to me needing to know how to go about tracing the queries executed on an oracle db along with their times etc... all the info possible.

In MSSql we use SQL profiler to get all this, along with a nice history (that you can filter).
I guess my lack of experience with Oracle products leads me to this question.

What tools could one use for the job, where would I get them, and I have 10.2g I need to log as well as a new 12c I need to log. If a UI tool exists that would be nice, otherwise, if you will suggest text based or sqlplus based tools, please be thorough with links or description as to what to do to call up the needed app/command line.

I am from MSSQL world, please bear with me, due to a buyout I am needing to learn Oracle and do the switch. Thanks for all your patience! :)
Who is Participating?
sdstuberConnect With a Mentor Commented:
the question isn't closed yet it's simply pending.
sdstuberConnect With a Mentor Commented:
enterprise manager with diagnostic packs will let you capture and review sql statements.

you can also turn tracing on for any session and use tkprof command line utility to get a report of your traced sessions.

Toad is a great gui for Oracle, but pricey, especially with DBA module, which you'd probably want.
landerson999Author Commented:
>you can also turn tracing on
How do i do that if the session being called up is inside of a batch file that is run each night...
I run the sql file as an argument with a command line call to sqlplus inside the batch file...

I am just wondering how the   "alter session set sql_trace=true;"
would apply to the   "sqlplus user/pwd@MyDB @kill_meidbo_sessions.sql"
command I have.... would I need it inside the batch file one line above, or within the .sql file being passed inside as a parameter?

Is there a global way of tracing all incoming sessions and not just the one session (as in SQLProfiler, you just say log all events, and it does not seperate from sessions to session, that is waht the filters are for).

>tkprof command line utility
I can call the tkprof utility, but I would see a file for each session? This means if I have 500 different calls to sqlplus for queries in my batch file each being its own session, I will need to go through the tkprof 500 times? Is there not a better way of doing this?

Also, is this applicable for both 10.2g and 12c
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

landerson999Author Commented:
There are a few tools out there that read the trace files better then tkprof does...and they are out there free. I am surprised no one thought of suggesting them, such as TOAD, QueryAdvisor, etc...
landerson999Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for sdstuber's comment #a39577336
Assisted answer: 0 points for landerson999's comment #a39594602

for the following reason:

Toad was mentioned, but that was it, no other tools, and only one person answered except myself..
sdstuberConnect With a Mentor Commented:
Toad's trace viewer is quite nice but had already been mentioned.  I also like the HOTSOS profiler (also available from methodr) it's not free, but it's quite good.

I've also written my own trace analysis tools.  The files are just plain text and pretty easy to parse and work with.

>>> Is there a global way of tracing all incoming sessions

yes, but turning on tracing for the entire db can be very expensive unless you have few sessions and low activity.  just set sql_trace =true for the system, not just the session.
if you go that route, don't leave it on for long.

To turn tracing on for a session I recommend using dbms_monitor package.
You options for bind and wait capture as well as the statements themselves.
landerson999Author Commented:
Thank you, I will check HOTSOS, I am uncertain how to award points after the issue has been closed, had this been a week ago... sigh.
why the penalty grade?
landerson999Author Commented:
I am not sure, I had selected the 500 points split into 2 solutions, but it came out wrong.
I am not sure if someone here has the power to correct the mistake... ??
landerson999Author Commented:
Actually, i see it is split into 3 separate solutions... so i guess that is right.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.