Menu
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?
343
1 Answer. ORDER BY id ASC; In the above query, we used subquery with the TOP clause that returns the table with the last 5 records sorted by ID in descending order. Again, we used to order by clause to sort the result-set of the subquery in ascending order by the ID column.
SELECT * FROM ( SELECT * FROM yourTableName ORDER BY id DESC LIMIT 10 )Var1 ORDER BY id ASC; Let us now implement the above query. mysql> SELECT * FROM ( -> SELECT * FROM Last10RecordsDemo ORDER BY id DESC LIMIT 10 -> )Var1 -> -> ORDER BY id ASC; The following is the output that displays the last 10 records.
METHOD 1 : Using LIMIT clause in descending orderof specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending. Since Employee table has IDs, we will perform ORDER BY ID in our query.
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.
178
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
