Skip to Main Content
  • Questions

  • All sql statements from all users in a time range

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Bill.

Asked: February 01, 2012 - 6:04 pm UTC

Last updated: February 02, 2012 - 3:56 pm UTC

Version: 11.0

Viewed 50K+ times! This question is

You Asked

I want to list all SQL statements executed by all users/schemas within a certain time range (starting from a certain time), How do I do that? Basically what I want is the sequence of SQL statments executed after the user invoked an action. Please note, when the user (in dev environment, user means me) invoked an action, different software modules will execute some sql statement with different schemas.

Thanks,
Bill

and Tom said...

Well, unless you enable sql_tracing or auditing of every table - that won't be possible.

We don't track that - it would be exorbitantly expensive.

What we do have is ASH - the active session history. That will keep in memory for about the last hour - one second samples from every session. That means you'll get to see the long running sql a session did (high probability that the one second samples will see it) and you'll get the see much of the short, quick sql they do that they do often (again, high chance of getting sampled since they do it alot). But you'll not see EVERY sql in sequence. You'll get the gist of what the session did.


To get every sql statement, in sequence, you would probably want to enable sql-trace for that session. then a tkprof report can show you sql by sql what they did - in the order they did it.



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 02, 2012 - 2:54 pm UTC

will *ASH_HISTORY view will usefull to get such information?

Thanks
Tom Kyte
February 02, 2012 - 3:56 pm UTC

I discussed that in the answer?

In the HIST tables, you'll have snapshots that are about 10 seconds apart for the session. What you will see with that, what you will be able to determine, will be the long running sql (high chance of getting sampled) and some of the short running sql that is run A LOT (high chance of getting sampled).

The rest of the stuff that happens in between those (approximately) 10 second samples - you won't see it.

try this view it may useful

Kotireddy, August 27, 2012 - 7:53 pm UTC 

SQL> select SQL_FULLTEXT , LAST_LOAD_TIME  FROM gv$sql where LAST_LOAD_TIME is not null order by LAST_LOAD_TIME ASC  

try this at least search the view it may helpful some what

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.