CODEWITHSUNDEEP
sqloracle-database
jake
jake

Reputation: 101

How to find last 10 oracle queries by username?

Not sure if this is possible. I'm on 10g. The end result would look like this:

Username      | Date/Time            | sqltext 
jdoe          | 3/21/11 10:32:27     | select sum(total) from sales where in_date > '08-JAN-11' 
jdoe          | 3/21/11 10:32:21     | delete from products_old 
jdoe          | 3/21/11 10:32:18     | select item, description from products where item = 'blah' 
jdoe          | 3/21/11 10:32:06     | select count(item) from products 
jdoe          | 3/21/11 10:31:44     | describe products

It looks like v$sql stores almost(?) all of the sql queries ever sent, but what do I join that to to get a username, and date?

Upvotes: 10

Views: 15255

Answers (2)

vlfig
vlfig

Reputation: 365

If you're using JDBC, you can always use the Log4jdbc wrapper library. It logs all jdbc activity to - you guessed - log4j.

You can configure the log level for several event types, it shows the sql issued (with bind variables replaced with their values) and timing information. I use it all the time.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

If you have the enterprise edition and the performance and tuning pack (otherwise querying the AWR tables violates your license), the V$ACTIVE_SESSION_HISTORY view will be the closest you'll get. This will capture at each second what each active session was executing. If you have a user that is executing many SQL statements per second or your SQL statements are fast enough that they aren't active across a particular second boundary, however, not every query would be captured. If you're just trying to get a general sampling of the last 10 things that a particular user has been done (with a bias to catching longer running queries), the AWR should be sufficient. But if you are trying to do something like trace what a user is doing in their session, the AWR would not be appropriate.

If you want to capture absolutely everything a user does, you would need to trace the session. That will cause a rather voluminous trace file to be generated on the server which you can summarize using the tkprof utility. But that requires that you enable tracing for a particular session before the SQL of interest is executed-- it's not something that can be done retroactively.

If you were interested only in the changes the session made, you could use LogMiner to go through the redo logs to see what the user was doing. That can be done retroactively but since SELECT statements don't generate REDO, they wouldn't be written to the redo logs and would be invisible to LogMiner.

Upvotes: 6

Related Questions