Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, shivashankar.

Asked: July 15, 2016 - 9:59 pm UTC

Last updated: July 18, 2016 - 3:29 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hello,

Using Oracle data dictionary, how to find out the list of tables that have undergone Insert/Update/Delete by a particular user account in the last 7 days? Also, if possible I want to know the number of transactions happened and the size of transactions.

Thanks,





and Connor said...

By default, we dont track that. If you have enabled auditing, then you could get the information from the audit views.

Alternatively, if you have all of the redo and archived redo logs available from the last 7 days, you could use LogMiner to trawl through all of the activity.

http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL019

Rating

  (1 rating)

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

Comments

follow up,

shivashankar nagaraj, July 18, 2016 - 2:04 pm UTC

Thank for providing log miner options. Besides this, I am wondering if there is an option to query v$sql (to extract the sql text) for the sqlIDs that belong to a particular user id. I know this is not a direct option but would it work (at least to a certain extent)?

Chris Saxon
July 18, 2016 - 3:29 pm UTC

v$sql has a parsing_user_id, parsing_schema_id and parsing_schema_name. But this only shows the details of the first user to execute the statement. So if you have multiple users running the same DML, you'll not see everything.

Also infrequently executed statements will be aged out over time. Unless your application sticks to the same few operations, there's a good chance at least some will no longer appear in v$sql.

So it would work to *some* extent. The problem is, you don't know what extent!

Chris
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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.